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.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
Make one copy of multiple worksheets one time into active workbook or another workbook with Move or Copy command
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:
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.
4. And then click OK, it will copy the selected worksheets one time into the specified workbook. See screenshot:
Note: You can also copy the worksheet to another opened workbooks or a new workbook.
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
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.
4. Then click OK, the specified worksheet has been copied 100 times in the active workbook.
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.
After installing Kutools for Excel, apply Copy Multiple Worksheets according to these steps:
1. Click Enterprise > Worksheets Tools > Copy Multiple Worksheets. See screenshot:
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.
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: