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

Before demonstrating the examples of Excel macros to create new sheet and copy data, let’s first learn how to add and apply a macro to a Workbook.

Open workbook and press ALT+F11 to open Visual Basic Editor.
From the Insert tab, select the Module option to create a new module.

Macro to Create New Sheet and Copy Data

Paste the required VBA code.

Macro to Create New Sheet and Copy Data

To run the code, click on the Run icon or click on the Run Sub/UserForm option from the Run tab, or press the F5

Example 1 – Macros to Create a Single Worksheet with Copied Data

1.1 To create a New Sheet without Manual Renaming and Copy Data

The code is:

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

Note:

ActiveSheet.Copy 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.

1.2 Create New Sheet with a Name Predefined in Macro

Use the following code.

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

Note:

Enter the 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 an error.

1.3 Rename Copied Sheet with InputBox

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

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 add the On Error Resume Next statement after lines 2 and 5.
Enter a desired name in the pop-up box inside the double quotes of InputBox(” “, ” “).

1.4 Rename Copied Sheet Based on Active Cell Value

You can create a new sheet and copy data with the following data. The difference is that, 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

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 the filepath of the desired closed workbook 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.
Enter the sheet name in SampleClosedWorkbook.Sheets(“Calculation”).Copy macro. Our sheet name is Calculation so we entered that.

3. Macro to Open Multiple New Sheets and Copy Data

Enter the following code:

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

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.

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, this macro will be Set SrcWSh = Worksheets(“Source Data”)