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


arrow blue right bubble 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:

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.

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

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

gold star1200 New Features for Excel, Make Excel Much Easy and Powerful:

  • 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

Comments  

Permalink +2 MichaelTadashi
Using the VBA code, the naming of the duplicated worksheets is in reverse order. Let's say I make 10 copies of Sheet1, I'll end up with Sheet1, Sheet1(10), Sheet1(9), Sheet1(8),..... ..is it possible to have them in normal order?
2014-03-05 03:43 Reply Reply with quote Quote
Permalink +9 Theou Aegis
I tried the VBA code and got subscript out of range error (9). I replaced the sheet name with anything I could think of that was in the workbook already. Any idea what I did wrong?

Also, is there a way to make it name each sheet incrementally? This would tie in with Michael's question, I'm sure. I'm guessing the answer to his question would be you'd need to set the After target to "sheet"+x somehow and that in turn would apply to my question if there was a way to specify what to name the new sheet. I could always just change my boss' "master" to "Aug 0" or whatever month it is, but it'd be easier for her to understand if the script did the naming automatically.
2014-08-04 18:52 Reply Reply with quote Quote
Permalink +2 Dee
thanks, the vba code was huge
2014-10-06 18:52 Reply Reply with quote Quote
Permalink -1 Lim
Wow, thanks for the VBA code. It really helps a lot!
2014-11-25 02:03 Reply Reply with quote Quote
Permalink +1 juan
Quoting MichaelTadashi:
Using the VBA code, the naming of the duplicated worksheets is in reverse order. Let's say I make 10 copies of Sheet1, I'll end up with Sheet1, Sheet1(10), Sheet1(9), Sheet1(8),.......is it possible to have them in normal order?


Anyone was able to answer this question? I need to create 72 copies, but it would be needed to have them in order (1 throught 72, intead of 72 through 1)

Thanks!
2015-02-17 19:25 Reply Reply with quote Quote
Permalink +1 Schuyler
Quoting juan:
Quoting MichaelTadashi:
Using the VBA code, the naming of the duplicated worksheets is in reverse order. Let's say I make 10 copies of Sheet1, I'll end up with Sheet1, Sheet1(10), Sheet1(9), Sheet1(8),.......is it possible to have them in normal order?


Anyone was able to answer this question? I need to create 72 copies, but it would be needed to have them in order (1 throught 72, intead of 72 through 1)

Thanks!


If you want the sheet copies to be in sequential order instead of backwards, change the following line...

After:=ActiveWorkbook.Sheets("Sheet1")

to this...

After:=ActiveWorkbook.Sheets(Worksheets.Cou nt)

My completed code looked like the following which uses 2 InputBox prompts to allow for a dynamic copy count and worksheet name..


Sub Copier()
Dim s As String
Dim numtimes As Integer
Dim numCopies As Integer
numCopies = InputBox("How many copies do you need?")
s = InputBox("Enter the name of the Worksheet you want to copy")
For numtimes = 1 To numCopies
ActiveWorkbook.Sheets(s).Copy _
After:=ActiveWorkbook.Sheets(Worksheets.Cou nt)
Next
End Sub
2015-08-31 20:56 Reply Reply with quote Quote
Permalink 0 Karsten
How do i get the coppies continuous numbered. If the sheet i want to copy is named I002, and i want the next to be named I003,I004,I005 an so on.
2017-01-20 06:59 Reply Reply with quote Quote
Permalink 0 Gopal Krishan
Search for word "After" and change it to "Before". This will create copies in right order "Before" Sheet1. The only thing to rememeber is that numbering will always start from (2) as the original sheet will always be counted as 1st copy. Also you can replace "Sheet1" with name of the tab you are trying to copy.
2015-06-26 00:10 Reply Reply with quote Quote
Permalink 0 sadaqat
yes it works thanks I have successfully make multiple copies of a single worksheet in same workbook by using vba code thnx a lot.
2015-08-29 12:12 Reply Reply with quote Quote
Permalink 0 Roy
how to copy with same column widths
2015-11-16 13:41 Reply Reply with quote Quote
Permalink 0 Schuyler
Same column widths as the original Worksheet or do you want all of the column widths to be the same?
2015-11-17 17:31 Reply Reply with quote Quote
Permalink 0 Debbi
I got the same error as Theou and no one seems to have addressed it. My tabs are already named PO 51, PO 52, etc. and I replaced Sheet1 with PO 51 to copy that and got the subscript error out of range (9)

I followed Schuyler's code to get the right order, but I still get the error and it's always due to these two lines:

ActiveWorkbook.Sheets(s).Copy _
Before:=ActiveWorkbook.Sheets(Worksheets.Count)
2016-01-28 01:09 Reply Reply with quote Quote
Permalink 0 Schuyler
Quoting Debbi:
I got the same error as Theou and no one seems to have addressed it. My tabs are already named PO 51, PO 52, etc. and I replaced Sheet1 with PO 51 to copy that and got the subscript error out of range (9)

I followed Schuyler's code to get the right order, but I still get the error and it's always due to these two lines:

ActiveWorkbook.Sheets(s).Copy _
Before:=ActiveWorkbook.Sheets(Worksheets.Count)


Can you include your complete code to make it easier to debug? A "subscript error out of range" usually means that the code references something that doesn't exist. I find this in my own code when I've got a typo or something of that nature.
2016-02-05 14:40 Reply Reply with quote Quote
Permalink 0 Susan
I get an error that 'numtimes' is not defined...?
2016-02-05 18:25 Reply Reply with quote Quote
Permalink 0 stalag 17
where i will insert this above code in vba should i create common button then inside ?? regards.
2016-07-19 04:36 Reply Reply with quote Quote
Permalink 0 Barry
When I first tried it, I got the error message because I didn't change the name Sheet1. After I realized what caused the error, I researched a little further, as I did not want to be manually entering the sheet name into the macro. And when I need this feature, it is almost always for the current sheet.

I added the line a = activesheet.name
And revised the line after:=activewo rkbook.sheets(" sheet1") to activeworkbook. sheets(a).copy _

That worked very well, but I did notice the numbering was reversed ... that didn't bother me as I was going to manually rename the new sheets anyway.

When I saw Schuyler's post, I further revised the line activeworkbook. sheets(a).copy _ to after:=activeworkbook.sheets(worksheets.count)

I am now satisfied with the result. My finished macro:

Sub copies()
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(Worksheets.Count)
Next
End Sub

---
All the best, Barry
2016-09-17 05:12 Reply Reply with quote Quote
Permalink 0 Simon
Fantastic VBA code - really helped
2017-01-17 17:09 Reply Reply with quote Quote

Add comment


Security code
Refresh