Macro to Create New Sheet and Copy Data in Excel (4 Examples)

If you want to easily open new worksheets and copy data, using Excel Macro is a perfect choice. In this article, I will show you how to use Excel macro to create a new sheet (or multiple sheets) and copy data to them.


Download Practice Workbook

Download the following Excel workbook for your self-practice.


How to Add and Apply a Macro to Workbook

Before demonstrating the examples, we will describe how to apply each of the codes. Execute the following quick steps to add macros to your workbook and run them.

📌 Steps to Add Macro to Workbook and Apply:

  • Open your workbook and press ALT+F11. This will take you to Visual Basic Editor.
  • Now from the Insert tab, select the Module option to create a new module.

Macro to Create New Sheet and Copy Data

  • Paste your required VBA code here.

Macro to Create New Sheet and Copy Data

  • Click on the Run icon, or click on the Run Sub/UserForm option from the Run tab. Or press the F5 key. This will run the code.


4 Examples of Macro to Create New Sheet and Copy Data in Excel

Now we will see 4 VBA examples to create new sheets (single or multiple sheets) and copy data into them.

1. Macros to Create a Single Worksheet with Copied Data

In the first example, we will create a new sheet only with 4 sample VBA codes and copy data into it. You can use any of them. The first code creates a new sheet with the name Excel gives it by default. In the next 3 codes, you can define the copied sheet name while writing the code, or in an InputBox, or rename it based on the active cell value. Use the one that suits your cause.

1.1 Simply Create a New Sheet without Manual Renaming and Copy Data

The code is as follows.

Public Sub CreateNewSheeAndCopy()
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
End Sub

Note:

  • Here, ActiveSheet.Copy method will create a new sheet with copied data from the active sheet.
  • Typing After:=Worksheets(Sheets.Count) will ensure the placement of the new sheet after the last worksheet.

Read More: How to Add Sheet with Name in Excel VBA (6 Easy Ways)


1.2 Create New Sheet with a Name Predefined in Macro

You can define the new sheet in the macro if you want.

Use the following code.

Public Sub RenameSheetInCode()
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = "Copied Data"
End Sub

Note:

  • Just rewrite your desired name in “ “ of  ActiveSheet.Name = “Copied Data” macro.
  • On Error Resume Next statement ensures that if the VB program finds any error in the macro next to this statement, it will execute the following macro. So, you can ignore this statement. It will not create any basic change in output. But it will secure your code from stopping due to error.

Read More: How to Add Sheet with Name from Cell Using Excel VBA


1.3 Rename Copied Sheet with InputBox

In this code, we will add an InputBox. So when you run the code, a window will pop up and ask you to input desired sheet name. Other functions are all the same.

The code is given below.

Public Sub RenameSheetWithInputBox()
Dim RenameSheet As String
RenameSheet = InputBox("What will be the new worksheet name?", "Rename Worksheet")
If RenameSheet <> "" Then
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = RenameSheet
End If
End Sub

Note:

  • You can also add the On Error Resume Next statement here, after lines 2 and 5.
  • Write as you wish to show in the pop-up box inside the double quotes of InputBox(” “, ” “).

Read More: How to Create New Sheet from Template Using Macro in Excel


1.4 Rename Copied Sheet Based on Active Cell Value

You can also create a new sheet and copy data with the following data. The difference is- this time the new sheet name depends on the active cell value of the current sheet.

Public Sub CopySheetAndRenameByActiveCell()
Dim newSheetName As String
On Error Resume Next
newSheetName = InputBox("Do You Want to Use This as New Worksheet Name?", "", ActiveCell.Value)
If newSheetName <> "" Then
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = newSheetName
End If
End Sub

2. Macro to Create New Sheet and Copy Data from a Closed Workbook

If you want to open a new sheet in the current workbook and copy data from a closed one to it, you can use the following VBA code.

Public Sub OpenNewSheetAndCopyDataFromClosedWorkbook()
Dim SampleClosedWorkbook As Workbook
Application.ScreenUpdating = False
Set SampleClosedWorkbook = Workbooks.Open("D:\SOFTEKO- Mahdy\Copy to New Sheet\Sample Closed Workbook.xlsx")
SampleClosedWorkbook.Sheets("Calculation").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
SampleClosedWorkbook.Close
Application.ScreenUpdating = True
End Sub

How to Use This Code:

  • Copy your desired closed workbook address and paste it inside Workbooks.Open() command within double quotes.
  • You have to know the sheet name from which you are extracting data to your new sheet.
  • Put the sheet name in  SampleClosedWorkbook.Sheets(“Calculation“).Copy macro. Ours’ name was Calculation. So we put it so.

Read More: Excel VBA to Add Sheet to Another Workbook (3 Handy Examples)


3. Macro to Open Multiple New Sheets and Copy Data

The VBA codes we have discussed, open a single sheet in a single run. If you want to open multiple sheets, you can use the following code. This code will open multiple new sheets and copy data from the active worksheet.

Public Sub CreateMultipleSheetsWithCopiedData()
Dim k As Integer
On Error Resume Next
k = InputBox("Copying Data form Current Sheet. How Many Duplicates Do You Want?")
If k >= 1 Then
For numtimes = 1 To k
ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
Next
End If
End Sub

Read More: Excel VBA: Add Sheet After Last (3 Ideal Examples)


4. Macro to Copy Data for Each Unique Item in New Sheet

If you have a large dataset and want to create individual worksheets for each unique item and copy data to them, you can use the following VBA code.

The code is originally from www.bluepecantraining.com.

Sub CreateSheetForIndividualItem()
Dim Items As Range
Dim Item As Range
Dim NewWSh As WorkSheet
Dim WSh As WorkSheet
Dim WShFound As Boolean
Dim SrcWSh As WorkSheet
Set SrcWSh = Worksheets("Order Details")
Set Items = SrcWSh.Range("E5", SrcWSh.Range("E5").End(xlDown))
Application.ScreenUpdating = False
For Each Item In Items
For Each WSh In ThisWorkbook.Worksheets
If WSh.Name = Item Then
WShFound = True
Exit For
Else
WShFound = False
End If
Next WSh
If WShFound Then
Item.Offset(0, -3).Resize(1, 13).Copy Destination:=Worksheets(Item.Value).Range("B2").End(xlDown).Offset(1, 0)
Else
Set NewWSh = Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
NewWSh.Name = Item
SrcWSh.Range("B4", SrcWSh.Range("B4").End(xlToRight)).Copy Destination:=NewWSh.Range("B2")
Item.Offset(0, -3).Resize(1, 13).Copy Destination:=NewWSh.Range("B3")
End If
Next Item
For Each WSh In ThisWorkbook.Worksheets
WSh.UsedRange.Columns.AutoFit
Next WSh
Application.ScreenUpdating = True
End Sub

How to Apply the Code for Your Case:

  • Set SrcWSh = Worksheets(“Order Details”)

Substitute Order Details with your Original Data Source Worksheet. If your source worksheet name is Source Data, then this macro will be Set SrcWSh = Worksheets(“Source Data”)

  • Set Items = SrcWSh.Range(“E5”, SrcWSh.Range(“E5”).End(xlDown))

Detect the column based on whose data, you want to create new sheets. Then input the first cell instead of E5.

  •   Item.Offset(0, -3).Resize(1, 13).Copy Destination:=Worksheets(Item.Value).Range(“B2”).End(xlDown).Offset(1, 0)

Input the cell reference form where you want to get the new sheets’ data started instead of B2.

Read More: Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)


Conclusion

In this article, we have shown 4 macros to create a new sheet and copy data in Excel. If you have any queries regarding them, please leave us a comment. And click here to find more such Excel-related content.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo