How to copy multiple sheets multiple times in Excel?

Excel’s built-in Move or Copy command provides a straightforward way to duplicate worksheets. This method can be helpful when you need to make a single backup of your original data sheets, share selected sheets with colleagues, or transfer specific worksheets to another file. However, it is most effective when you only need one copy of each worksheet at a time, as creating multiple copies requires repeating the process manually.

1. In the workbook where you want to copy worksheets, select the sheets you wish to duplicate from the Sheet Tab bar.
Note: Hold down the CTRL key to select multiple non-adjacent sheet tabs by clicking each one individually. To select a series of adjacent sheets, click the first sheet tab, hold down SHIFT, and click the last tab in the sequence.

2. Right-click any selected tab and choose Move or Copy from the context menu. This opens the Move or Copy dialog box.

3. In the Move or Copy dialog box, adjust the following options as needed for your situation:
(1) Use the To book drop-down menu to specify the workbook you want to copy sheets to. You can select the current (active) workbook, another workbook you already have open, or choose to create the sheets in a brand new workbook.
(2) Set the location for the copied sheets in the Before sheet box. Typically, placing the copies after all existing sheets makes them easier to find.
(3) Before clicking OK, make sure to check the Create a copy option. If you do not select it, the sheets will be moved (removed from the original) to the destination instead of copied.
(4) Click the OK button to complete the copy operation.

Once you confirm, Excel creates one copy of each selected worksheet in the designated workbook and location.

If you need more than one copy of the same worksheet(s), repeat this process as many times as required. Be aware that with multiple repetitions, sheet names may reflect incrementing numbers (e.g., “Sheet1 (2)”, “Sheet1 (3)”), making it easier to track the duplicated versions.
Applicable scenarios: Best for backing up original worksheets, moving selected sheets between files, or quickly duplicating a limited number of sheets.
Pros: Built-in, no add-ins required, easy for one-time copies.
Cons: Not efficient when multiple copies are needed; manual repetition is required for each additional copy.

Tips & Cautions:

• If you're duplicating sheets to a new workbook, remember to save the new file under a meaningful name to prevent data loss.
• Certain references or formulas pointing to other worksheets might require attention after copying, especially when moved to a new file.
• Sheet protection, hidden status, or macros may or may not be retained, depending on your workbook’s setup—double-check after copying if these features are important.

If you want to duplicate several worksheets more than once within the same workbook, manually repeating the Move or Copy command can be very time-consuming. Instead, the Copy Worksheets feature included in Kutools for Excel significantly streamlines this process—allowing you to make multiple copies of several worksheets at once, all in a single step.

1. Click Kutools Plus > Copy Worksheets on the Ribbon. This opens the Copy Multiple Worksheets wizard.

Note: Alternatively, you can access the same feature via Kutools Plus > Worksheet > Copy Worksheets.

2. In the Copy Multiple Worksheets dialog box, set up your preferences:
(1) Select the worksheets to copy by checking them in the Copy the selected worksheets list.
(2) Specify how many copies you wish to create in the Number of copies box. For example, enter “3” to make three copies of each selected sheet.
(3) Set the position for the new copied sheets (e.g., place after current worksheet, before all worksheets, etc.)
(4) When done, click Ok to proceed.

3. A confirmation dialog will appear, letting you know the selected worksheets have been successfully copied the specified number of times. Click OK to close this prompt.

This feature makes it extremely convenient to duplicate multiple worksheets several times without the need for repetitive manual steps.
Applicable scenarios: Highly recommended for efficiently creating sample forms for multiple people, batch copying sheets for recurring monthly tasks, or quickly preparing templates for various departments.
Pros: Saves time with bulk operations, easy-to-follow steps, no manual repetition.
Cons: Requires the Kutools for Excel add-in to be installed.
Troubleshooting & Tips:

• If you need to copy all sheets at once, use the “Select All” feature in the wizard.
• Double-check that the number of copies and selected sheets are correct before confirming, as mass duplications can require thorough review to avoid confusion.
• If your workbook is protected, temporarily remove protection before using this tool, then reapply if necessary afterwards.

If you don't have Kutools for Excel yet, you can explore the utility's features risk-free with a trial version. Have a Free Trial!

When you need to create a significant number of copies (for example, 10 or even 100 copies) of a single worksheet, using Excel’s standard features can become quite cumbersome. Instead, you can automate the process using a simple VBA macro. This is especially useful for generating a workbook with preset templates or duplicating a form for multiple data entries, such as for surveys or invoices.

1. Press ALT + F11 to open the Microsoft Visual Basic for Applications window.

2. Go to Insert > Module, and paste the following code into the module window:

VBA: Make multiple copies of a certain worksheet into active workbook

Sub Copier ()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub

Note: Please replace "Sheet1" within the code to the actual name of the sheet you want to duplicate. Make sure that the sheet exists in your workbook before running the code, or you may get an error.

3. Press F5 to run the code. You’ll be prompted to enter the number of copies you wish to create for your specified worksheet.

4. After entering your desired number and confirming, the macro will quickly generate the requested copies, each with a unique name (e.g., “Sheet1 (2)”, “Sheet1 (3)”, etc.).

Applicable scenarios: Use this approach when you need to mass-produce identical sheets for data entry, workflow templates, or examination forms.
Pros: Automates repetitive tasks, highly customizable.
Cons: Requires basic comfort with the VBA editor, and may require macro permissions to be enabled in your workbook.
Troubleshooting and Tips:

• If your workbook is saved as .xlsx, remember to save as .xlsm to retain macros.
• If your macro fails, double-check the sheet name spelling in the code and ensure there are no naming conflicts.
• If your macro security is set to High, adjust settings to allow macro-enabled workbooks to run VBA code.

In scenarios where you need to consolidate worksheets from several different workbooks, for example when gathering input from multiple team members or merging monthly reports, Kutools for Excel's Combine Worksheets feature is particularly helpful. With just a few steps, you can extract and combine specific sheets from multiple closed files directly into a new workbook.

1. Go to Kutools Plus > Combine to activate the Combine Worksheets wizard.

2. In Step 1 of the wizard, choose the Combine multiple worksheets from workbooks into one worksheet option, then click Next.

3. In Step 2, follow these steps for selecting worksheets across workbooks:
(1) Click Add > File or Folder to select the files from which you want to extract worksheets.
(2) In the Workbook list, check the files you’re interested in.
(3) In the Worksheet list, check the worksheet(s) you wish to copy from each file.
(4) Repeat for each workbook you want to include.
(5) After making selections, click Next.

4. In Step 3, configure any additional copying settings according to your needs, such as how data and formatting should be combined. Click Finish to complete the operation.

5. Specify a destination folder and file name for the new workbook in the dialog box that appears, and click Save.

Finally, you may be prompted whether to open the new workbook and save the combination scenario for future use. Make your selections as needed. All checked worksheets from the selected workbooks will now have been successfully merged into a single new file.

The Combine Worksheets utility is highly versatile:

• Quickly combine multiple worksheets or ranges from different files into one worksheet.
• Merge all same-named worksheets from several workbooks into one summary sheet.
• Consolidate or merge entire workbooks for easy management.
• Summarize or calculate data from many sources in a single place.

Pros: Convenient for merging data from separate people or projects, saves time in routine reporting.
Cons: Requires installation of Kutools for Excel.
Make sure to review merged data for duplicate or missing information, especially after combining large or similar worksheets.

If you haven’t tried this utility yet, Have a Free Trial!

Although Excel formulas cannot duplicate entire worksheets, a semi-manual alternative is to prepare a new worksheet template and use formulas to pull data from the original. This approach is useful when you only need data replication—such as for reporting or preparing templates—and not full duplication of charts, cell styles, or macros.

1. Create a new worksheet, then use cell references or formulas to replicate the data you want. For example, in cell A1 of your new sheet, enter:

=Sheet1!A1

2. Drag the fill handle to copy this formula to the desired range. This links each cell on the new sheet to its counterpart on the source sheet.

3. To create additional copies, duplicate the new worksheet and update the formulas or references as needed (e.g., change references to another source sheet if required).

Applicable scenarios: Good for report templates or when only data (not formatting or code) needs to be repeated.
Pros: No extra tools needed.
Cons: Manual effort required when high volume is needed; does not duplicate advanced formatting, shapes, or VBA.

Tips: After formula-based linking, you can use Paste Values to convert formulas to values if you want static data instead of linked cells.

If you have a list of sheet names that you frequently need to duplicate, you can use VBA to copy all these sheets at once, with minimal manual input. This is especially useful for templates or standardized forms distributed monthly, quarterly, or for training batches.

1. Press ALT + F11 to open the Visual Basic for Applications window.

2. In the VBA editor, click Insert > Module, then copy and paste the code below into the new module window:

Sub CopyListSheets()
    Dim wsName As Variant
    Dim CopyCount As Integer
    Dim i As Integer
    Dim sheetExists As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    wsNames = Array("Sheet1", "Sheet2") 'Edit these names to your target sheets
    
    CopyCount = Application.InputBox("Enter the number of copies:", xTitleId, Type:=1)
    
    For Each wsName In wsNames
        sheetExists = False
        
        For Each ws In Worksheets
            If ws.Name = wsName Then
                sheetExists = True
                Exit For
            End If
        Next
        
        If sheetExists Then
            For i = 1 To CopyCount
                Worksheets(wsName).Copy After:=Sheets(Sheets.Count)
            Next i
        End If
    Next wsName
End Sub

3. Press F5 to run the code. When prompted, enter the number of copies you want for each sheet. The specified sheets will each be copied the given number of times, added to the end of your workbook.

Note: Adjust the wsNames = Array("Sheet1", "Sheet2") line to include the names of all sheets you want duplicated. This solution is ideal for batch-processing standard-form sheets.

Tips & Error Reminders:

• If any worksheet name is spelled incorrectly or missing, that sheet will be skipped.
• Ensure you do not exceed Excel’s worksheet limits (usually up to 255 sheets per workbook, but large files may be slowed down before that).