Excel VBA to Copy and Rename a Worksheet Multiple Times

Below is a sample sheet named “Dataset” which we will use to illustrate how to duplicate a sheet multiple times and rename it in Excel using VBA.

Build The Code

Step 1: Open the VBA Editor

Press Alt + F11 to open the VBA Editor. You must save your Excel file in macro-enabled workbook (.xlsm) format.

Step 2: Create the Sub Procedure

Copy the following code to the VBA Editor:

Sub copy_multiple_times_rename()

End Sub

We will execute our code here.

Step 3: Declare Necessary Variables

Sub copy_multiple_times_rename()

    Dim i As Long

    Dim num_of_sheets As Integer

    Dim sheet_name As String

    Dim current_sheet As Worksheet

End Sub

Here,

i = Will be used it for the loop.

num_of_sheets = Will store the number of copies you want to make.

sheet_name = Will store the sheet name.

current_sheet = Will store the active sheet.

Step 4: Set the Sheet that You Want to Copy

Sub copy_multiple_times_rename()

    Dim i As Long

    Dim num_of_sheets As Integer

    Dim sheet_name As String

    Dim current_sheet As Worksheet

    On Error Resume Next

    Application.ScreenUpdating = False

    Set current_sheet = ActiveSheet

End Sub

Application.ScreenUpdating = False: It will help us to run the code in the background.

Set current_sheet = ActiveSheet: It will activate the sheet that you want to copy.

Step 5: Create the User Input for Number of Sheets

Sub copy_multiple_times_rename()

    Dim i As Long

    Dim num_of_sheets As Integer

    Dim sheet_name As String
    Dim current_sheet As Worksheet
    On Error Resume Next

    Application.ScreenUpdating = False
    Set current_sheet = ActiveSheet
    num_of_sheets = InputBox("Enter number of times to copy the current sheet")

End Sub

num_of_sheets = InputBox(“Enter number of times to copy the current sheet”): It will show an input box to take the number of sheets the user wants to make.

Step 6: Copy Worksheet Multiple Times and Rename It Using Loop

Sub copy_multiple_times_rename()

    Dim i As Long
    Dim num_of_sheets As Integer
    Dim sheet_name As String
    Dim current_sheet As Worksheet

    On Error Resume Next

    Application.ScreenUpdating = False

    Set current_sheet = ActiveSheet

    num_of_sheets = InputBox("Enter number of times to copy the current sheet")

    For i = 1 To num_of_sheets
        sheet_name = ActiveSheet.Name
        current_sheet.Copy After:=ActiveWorkbook.Sheets(sheet_name)
        ActiveSheet.Name = "Copy-" & i

    Next

End Sub

We will run the loop.

sheet_name = ActiveSheet.Name: It will store the sheet name of “Dataset”.

current_sheet.Copy After:=ActiveWorkbook.Sheets(sheet_name)”: It will copy the “Dataset” sheet and paste it after the “Dataset” worksheet.

ActiveSheet.Name = “Copy-” & i: This renames the sheet. We will rename the worksheet starting with “Copy-”.

Step 7: Set the “Dataset” Sheet as Active Sheet

Sub copy_multiple_times_rename()

    Dim i As Long
    Dim num_of_sheets As Integer
    Dim sheet_name As String
    Dim current_sheet As Worksheet

    On Error Resume Next

    Application.ScreenUpdating = False
    Set current_sheet = ActiveSheet

    num_of_sheets = InputBox("Enter number of times to copy the current sheet")

    For i = 1 To num_of_sheets
        sheet_name = ActiveSheet.Name
        current_sheet.Copy After:=ActiveWorkbook.Sheets(sheet_name)
        ActiveSheet.Name = "Copy-" & i
    Next

    current_sheet.Activate
    Application.ScreenUpdating = True

End Sub

current_sheet.Activate: it will activate the “Dataset” sheet.

Run the Code

Steps

Press Alt + F8 or click on Macros on the Developer tab to open the Macro dialog box.

Select copy_multiple_times_rename and click on Run.

Enter the number of copies you want to make. Click on OK.

This VBA code will copy the worksheet multiple times and rename it.

More VBA Codes to Copy and Rename Excel Worksheets

Code 1 – VBA Code to Copy Sheet and Rename Based on Cell Value

We will copy our sample sheet named “Dataset” and rename based on the cell value “Arrival Time”.

Add the following VBA code and run the macro:

Sub Copy_rename_sheet()

Dim sh As Worksheet

Set sh = Sheets("Dataset")

sh.Copy After:=Sheets(Sheets.Count)

If sh.Range("B4").Value <> "" Then

    ActiveSheet.Name = sh.Range("B4").Value

    End If

    sh.Activate

End Sub

This VBA will copy the worksheet and rename it based on the cell value in Excel.

Code 2 – VBA Code to Copy Sheet and Rename with Date

The following VBA code will copy the sheet and rename it with the current date based on your device:

Sub copy_sheet_with_date()

    Dim sheet_name, date_today As String
    Dim current_sheet As Worksheet
    
Set current_sheet = Sheets("Template")
    date_today = Format(Date, "DD-MM-YY")
    current_sheet.Copy After:=Sheets(Sheets.Count)
    sheet_name = date_today
    ActiveSheet.Name = sheet_name

End Sub

Set current_sheet = Sheets(“Template”): Selects the sheet you want to copy.

date_today = Format(Date, “DD-MM-YY”): Store the date in a variable.

current_sheet.Copy After:=Sheets(Sheets.Count): Copy the “Template” sheet to the end.

sheet_name = date_today: Store date as a string to consider as the name.

ActiveSheet.Name = sheet_name: Change the new sheet name to the current date.

After running the code, you will see the output as shown:

Code 3 – VBA Code to Duplicate a Worksheet and Rename

Duplicate Sheet with a Fixed Name

To rename the sheet with the code, use the following VBA:

Sub duplicate_sheet_rename()

    Sheets("Template").Copy After:=Worksheets(Sheets.Count)

    On Error Resume Next

    ActiveSheet.Name = "New Template"

End Sub

After running the code, you will see the following:

Duplicate and Rename Sheet Using VBA (with User Input)

To rename the worksheet during duplication, use the following code:

Sub duplicate_sheet_user_input()

    Dim sheet_name As String

    On Error Resume Next

    sheet_name = InputBox("Enter The New Worksheet Name")

    If sheet_name <> "" Then

        ActiveSheet.Copy After:=Worksheets(Sheets.Count)

        On Error Resume Next

        ActiveSheet.Name = sheet_name

    End If

End Sub

This code will show an input box. Enter a name for the sheet and click OK.

It will duplicate the sheet and rename it with the given input.

Code 4 – VBA Code to Copy Sheet and Rename Based on List

We will copy the sample sheet “List” and rename the sheet based on the list of salespersons.

Use the following VBA and run the macro:

Sub copy_rename_list()

Dim current_sheet As Worksheet

Dim c As Range

Set current_sheet = Sheets("List")

    For Each c In current_sheet.Range("B5", current_sheet.Cells(Rows.Count, 2).End(xlUp))

        current_sheet.Copy After:=Sheets("List")

        ActiveSheet.Name = c.Value: ActiveSheet.Range("D5") = c.Value

    Next

End Sub

This VBA macro will copy the worksheet and rename it based on the list in Excel.

Copy Worksheet to Existing Worksheet Using Excel VBA Macros

To copy a worksheet’s content and paste it into your existing worksheet in the current workbook, you have to copy the entire range of cells of that worksheet. Or you can select a

particular range of cells to paste into the existing worksheet.

You can do this using the following VBA codes.

When Your Workbooks are Open But Aren’t Saved Yet

Sub copy_data()

Workbooks("Source").Worksheets("Dataset").Range("A:Z").Copy _

Workbooks("Destination").Worksheets("Sheet2").Range(""A:Z"")

End Sub

It will copy range from workbook Source and paste it to Destination workbook’s sheet number 2.

When Your Workbooks are Open and Saved

Sub copy_data2()

Workbooks("Source.xlsm").Worksheets("Dataset").Range(""A:Z"").Copy _

Workbooks("Destination.xlsm").Worksheets("Sheet2").Range(""A:Z"")

End Sub

Copy a Range to a Worksheet in Another Closed Workbook

Sub copy_data3()

Workbooks.Open "D:\SOFTEKO\Copy Sheet\Destination.xlsm"

Workbooks("Source").Worksheets("Dataset").Range(""A:Z"").Copy _

Workbooks("Destination").Worksheets("Sheet2").Range(""A:Z"")

Workbooks("Destination").Close SaveChanges:=True

End Sub

Remember: The given VBA code is a sample, and won’t work until you make a few changes to it. Make sure to change the workbook name, worksheet name, and the location of the workbook as needed.

Download Practice Workbook

Copy and Rename a Sheet Multiple Times.xlsm