Excel VBA to Copy and Rename a Worksheet Multiple Times

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, using VBA macros can solve a lot of problems with ease. A crucial procedure of VBA macros is to copy worksheets to another workbook or multiple workbooks in Excel. We can use these codes to copy data, copy sheets, or copy workbooks in Excel. In this advanced Excel tutorial, you will learn how to copy a worksheet multiple times and rename it using VBA macro. Not only that, but we are also providing you with several VBA codes to copy worksheets with a lot of other criteria. So, read the whole article to improve your Excel knowledge.


Copy Worksheet Multiple Times and Rename with VBA Code in Excel: Step-By-Step

In this section, I will show you a step-by-step procedure to duplicate a sheet multiple times and rename it in Excel. Sometimes, you need a template of a worksheet that you can use several times in the workbook. This method will help you to copy that template in Excel using VBA.

Remember, we are doing this by VBA code. The VBA codes will effectively perform these with ease. So, if you have an idea about VBA codes, try this.

Take a look at this dataset:

Copy and Rename a Worksheet Multiple Times

Here, we have a sheet named “Dataset”. Now, will copy multiple times and rename this worksheet using VBA in Excel. Follow the step-by-step breakdown.

Build The Code

📌 Step1: Open the VBA Editor

First, press Alt + F11 on your keyboard to open your VBA Editor. You must save your Excel file in macro-enabled workbook (.xlsm) format.

📌 Step 2: Create the Sub Procedure

Type the following code:

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 = We will use it for the loop

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

sheet_name = It will store the sheet name.

current_sheet = We will use this to 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 want 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

After taking the input, 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: By this, we rename the sheet. Basically, we will rename the worksheet starting with “Copy-”. You will see this later.

📌 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

Follow these steps to copy the worksheet multiple times and rename based on the VBA code.

📌 Steps

  • First, press Alt + F8 or click on Macros on the Developer tab to open the Macro dialog box.

  • Then, select copy_multiple_times_rename and click on Run.

Copy and Rename a Worksheet Multiple Times

  • After that, enter the number of copies you want to make. Then, click on OK.

Copy and Rename a Worksheet Multiple Times

In the end, this VBA code will copy the worksheet multiple times and rename it in your Excel workbook.

Read More: How to Copy a Sheet to Another Sheet in Excel


More VBA Codes to Copy and Rename Excel Worksheets

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

Now, you may be in a situation where you have to copy a sheet and rename based on a cell value. Now, that seems difficult, right? But, if you have an idea of the VBA code, you can easily resolve this.

Take a look at the following screenshot:

Here, we have a sheet named “Dataset”. You can see some data in the sheet. Now, we will copy this sheet and rename it based on the cell value. Here, we will rename the sheet name with “Arrival Time”.

Now, type 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

After running the macro, you will see the following output:

VBA: Copy Sheet and Rename Based on Cell Value

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


2. VBA Code to Copy Sheet and Rename with Date

Now, take a look at the screenshot:

Here, we have a sheet named “Template”. Basically, this sheet represents the sales of a day. Suppose, this dataset indicates the sales for today. And you want to copy the sheet and rename it with a date of today. Because you want to use the template and save this for each day’s sales information.

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 following output:

VBA: Copy Sheet and Rename with Date

As you can see, the above VBA code will successfully copy the worksheet and rename it with the current date in Excel.

Read More: Copy Worksheet to Another Workbook without Reference in Excel


3. VBA Code to Duplicate a Worksheet and Rename

If you have read the previous sections, these codes will be easier for you. You can easily duplicate a sheet and rename it using VBA codes. We are trying to show you two types of scenarios here.

Take a look at the screenshot:

Here, duplicate the “Template” sheet and rename it.

Duplicate Sheet with a Fixed Name

If you want to rename the sheet with the code, try 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:

VBA: Duplicate a sheet and Rename

Duplicate and Rename Sheet Using VBA (with User Input)

If you want to give the user to rename their worksheet according to their choice, try 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 that will take the name from the user.

VBA: Duplicate a sheet and Rename

After that, click on OK. It will duplicate the sheet and rename it with the user’s given input.

VBA: Duplicate a sheet and Rename

As you can see, we successfully duplicated the sheet and renamed it in Excel.


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

Now, in this section, you will learn to copy a sheet and rename based on a list. Take a look at the following screenshot:

Excel VBA: Copy Sheet and Rename Based on List

Here, we have a sheet “List”. Now, we will copy that sheet. Also, we are going to rename that sheet based on the list of salespersons. So, there will be three sheets.

To do this, type 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

After running the code, you will see the following output:

Excel VBA: Copy Sheet and Rename Based on List

As you can see from here, this VBA macro will copy the worksheet and rename it based on the list in Excel. Give this a try.

Read More: [Fixed!] Move or Copy Sheet Not Working in Excel


Copy Worksheet to Existing Worksheet Using Excel VBA Macros

We have learned VBA Codes to copy and rename Excel Worksheets. Now, you may want to copy a worksheet’s content and paste it into your existing worksheet in the current workbook. To do that, 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 by 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: I provided you with a sample VBA Code. Change the workbook name, worksheet name, and also the location of the workbook. Otherwise, it won’t work.


💬 Things to Remember

Your sheet name may not be as same as ours. So, change it according to you.

Remember to save your file in Macro-Enabled Workbook (.xlsm) format.

✎ You can also copy a worksheet to another workbook without opening it in various ways.


Download Practice Workbook


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge copy a worksheet multiple times and rename it in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Keep learning new methods and keep growing!


Related Articles


<< Go Back to Copy Sheet | Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo