How to copy multiple sheets multiple times in Excel?

Sometimes, we may need to make a copy or multiple copies of specified worksheets in a workbook. Here are some quick ways to help you easily copy one worksheet or multiple worksheets multiple times in Excel.

Make one copy of multiple worksheets one time into active workbook or another workbook with Move or Copy command

Make multiple copies of one specified worksheet into active workbook with VBA code

Make multiple copies of multiple worksheets into active workbook with Kutools for Excel

Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

arrow blue right bubble Make one copy of multiple worksheets one time into active workbook or another workbook with Move or Copy command

Hint


With the Move or Copy command in Excel, you can make one copy of a worksheet, multiple specific worksheet or all worksheets into active workbook or another workbook.

1. Activate the workbook you need to make a copy of multiple worksheets.

2. Hold down Ctrl key to select the worksheets you want to be copied, and right click on sheet tab and select Move or Copy option. See screenshot:

doc-copy-multiple-sheets1

3. In the Move or Copy dialog box, please specify the following settings:

  • (1.) From To book drop down list, you can specify the workbook where the worksheets will be copied into.
  • (2.) Specify the position of the copied sheets, you can choose it after all of the existing sheets.
  • (3.) Check Create a copy option, if you don’t check this option, the selected worksheets will be moved into another workbook.

doc-copy-multiple-sheets2

4. And then click OK, it will copy the selected worksheets one time into the specified workbook. See screenshot:

doc-copy-multiple-sheets3

Note: You can also copy the worksheet to another opened workbooks or a new workbook.


arrow blue right bubble Make multiple copies of one specified worksheet into active workbook with VBA code

If you want to make 100 copies of a specified worksheet, the Move or Copy function will be time consuming, and you must repeat the operation many times. But with the following VBA code, you can quickly copy the worksheet 100 times at once.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

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: In the above code, replace "Sheet1" with the name of the sheet to be copied.

3. Then press F5 key to run the code, a prompt box appears to ask you the number of sheet copies you want.

doc-copy-multiple-sheets4

4. Then click OK, the specified worksheet has been copied 100 times in the active workbook.


arrow blue right bubble Make multiple copies of multiple worksheets into active workbook with Kutools for Excel

Though it is simple to make a copy of multiple worksheets in Excel, if you want to make multiple copies of multiple worksheets in current workbook, you may have to use the Move or Copy command item again and again. Would like to get the thing done in one click? Just follow the following steps I am going to tell.

With the Copy Multiple Worksheets utility of the third party add-in Kutools for Excel, you are able to make multiple copies of multiple worksheets in one click in Excel.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.

After installing Kutools for Excel, apply Copy Multiple Worksheets according to these steps:

1. Click Enterprise > Worksheets Tools > Copy Multiple Worksheets. See screenshot:

doc-copy-multiple-sheets5

2. Apply settings in the Copy Multiple Worksheets dialog box:

  • (1.) Check the worksheets you want to be copied from Copy the selected worksheets option.
  • (2.) Specify The number of copies.
  • (3.) Determine the position of the copied sheets, for example, before or after all worksheets, before or after current worksheet.

doc-copy-multiple-sheets6

3. Then click OK button, a prompt box pops out to remind you the checked worksheets have been copied into multiple times as you need. See screenshot:

doc-copy-multiple-sheets7

Click this Copy Multiple Worksheets feature to know more.


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase