How to Duplicate a Sheet in Excel Multiple Times (2 Methods)

How to Duplicate a Sheet in Excel

Right-Click on the Worksheet:
- Open your Excel workbook.
- Locate the sheet you want to duplicate in the bottom tab bar.
- Right-click on the sheet tab to bring up the context menu.

Select “Move or Copy”:
- From the context menu, choose “Move or Copy.”
- This action will open the “Move or Copy” dialog box.

Choose the Destination:
- In the dialog box, you’ll see a list of sheets in your workbook.
- Select the sheet where you want to place the duplicate.
- If you want to move the duplicate to the end of the workbook, choose the Move to end option.

Create a Copy:
- Check the box labeled Create a copy.
- This ensures that a new sheet is created as a duplicate, leaving the original intact.

Click “OK”:
- Finally, click the OK button.
- Excel will create a duplicate of the selected sheet in the specified location.

You’ll now have a new sheet that’s an exact copy of the original.

Let’s explore how to duplicate a sheet multiple times in Excel using VBA code. I’ll break down the steps and provide examples for your understanding.

Method 1 – Duplicate a Sheet Multiple Times Using VBA

Scenario:

Suppose you have an Excel workbook with a sheet containing an annual loan payment calculator. You want to create multiple duplicate sheets, each with slight variations (e.g., different interest rates, payment amounts, or loan periods).

Steps:

Access the Developer Tab:
- Open your Excel workbook.
- If you don’t see the Developer tab on the ribbon, enable it:
  - Go to the File tab.
  - Click on Options.
  - In the Excel Options dialog, select Customize Ribbon.
  - Check the box next to Developer under the Main Tabs section.
  - Click OK.

Open the Visual Basic Editor (VBE):
- Click on the Developer tab.
- In the Code group, click on Visual Basic (or press Alt + F11).
- The Visual Basic for Applications (VBA) editor will open.

Insert a New Module:
- In the VBA editor, go to the Insert menu.
- Choose Module.
- A new module code window will appear.
Write the VBA Code:
- Copy and paste the following VBA code into the module:

Sub DuplicateSheet()
    Dim ws As Worksheet
    Dim i As Integer
    Dim numDuplicates As Integer
    
    ' Set the number of duplicates you want
    numDuplicates = 5
    
    ' Reference the sheet you want to duplicate
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Loop to create duplicates
    For i = 1 To numDuplicates
        ws.Copy After:=ws
    Next i
End Sub

Run the Macro:
- Close the VBA editor.
- Back in Excel, go to the Developer tab.
- Click on Macros in the Code group.
- In the Macro dialog box, select DuplicateSheet.

- Click Run.
- A message box will appear, asking how many times you want to copy the annual loan payment sheet.
- Enter the desired number and click OK.

View the Results:
- Excel will create the specified number of duplicate sheets after the original sheet.
- Each duplicate will have the same content as the original but can be modified independently.

Method 2 – Duplicate a Sheet Multiple Times and Rename Using VBA

Scenario:

You have an Excel workbook with a template worksheet that you want to use as a basis for creating multiple similar sheets. Each sheet will represent different scenarios, such as varying interest rates, payment amounts, or loan periods. By using VBA code, you can automate the process of duplicating and renaming these sheets.

Steps to Duplicate and Rename a Sheet Using VBA:

Access the Developer Tab:
- Open your Excel workbook.
- If you don’t see the Developer tab on the ribbon, follow these steps:
  - Click on the File tab.
  - Select Options.
  - In the Excel Options dialog, choose Customize Ribbon.
  - Check the box next to Developer under the Main Tabs section.
  - Click OK.

Open the Visual Basic Editor (VBE):
- Click on the Developer tab.
- In the Code group, click on Visual Basic (or press Alt + F11).
- The Visual Basic for Applications (VBA) editor will open.

Insert a New Module:
- In the VBA editor, go to the Insert menu.
- Choose Module.
- A new module code window will appear.

Write the VBA Code:
- Copy and paste the following VBA code into the module:

Sub copy_multiple_times_rename()
Dim ws As Worksheet
Dim i As Integer
Dim numDuplicates As Integer
Dim baseSheetName As String

' Set the number of duplicates you want
numDuplicates = 5

' Reference the template sheet you want to duplicate
Set ws = ThisWorkbook.Sheets("TemplateSheet")

' Base name for the duplicated sheets
baseSheetName = "Scenario"

' Loop to create duplicates and rename them
For i = 1 To numDuplicates
ws.Copy After:=ws
ActiveSheet.Name = baseSheetName & i
Next i
End Sub

Run the Macro:
- Close the VBA editor.
- Back in Excel, go to the Developer tab.
- Click on Macros in the Code group.

- In the Macro dialog box, select copy_multiple_times_rename.
- Click Run.

- A message box will appear, asking how many times you want to copy the template sheet.
- Enter the desired number and click OK.

View the Results:
- Excel will create the specified number of duplicate sheets based on the template.
- Each sheet will be named “Scenario1,” “Scenario2,” and so on.