Copy One Sheet Multiple Times

JamesKM · Oct 1, 2002

For various reasons (mainly my original macro is no good enough) I produce a report in a new workbook that contains one worksheet with the required data.
My question is can I copy this worksheet multiple times quickly without going through the Click, Ctrl, Drag thing (or Move and Copy.. thing) on lots of occassions?

Thanks in advance,
James

brownbear2006 · Feb 16, 2012

When I run the macro for 1 copy, it works fine. When I run the macro for 2 or more copies, I get the following 6 message (that I have to click YES to continue) for each additional copy I request (if I want 2 copies, I go through this loop/routine once; if I want 3 copies, I go through this routine twice, etc):

1) A formula or sheet you want to move or copy contains the name "Item", which already exists on the destination worksheet. Do you want to use this version of the name?
2) A formula or sheet you want to move or copy contains the name "item2012', which already exists on the destination worksheet. Do you want to use this version of the name?
3) A formula or sheet you want to move or copy contains the name 'LinkedArea22', which already exists on the destination worksheet. Do you want to use this version of the name?
4) A formula or sheet you want to move or copy contains the name 'LinkedArea24', which already exists on the destination worksheet. Do you want to use this version of the name?
5) A formula or sheet you want to move or copy contains the name 'LinkedArea26', which already exists on the destination worksheet. Do you want to use this version of the name?
6) A formula or sheet you want to move or copy contains the name '
TelNet', which already exists on the destination worksheet. Do you want to use this version of the name?

Is there a way to just have it make 20 copies of the one page instead of going to the original worksheet each time, right clicking on move/copy, and creating a copy? Or without having to go through all those 6 steps for each copy?

Thanks,
BB

brownbear2006 · Feb 16, 2012

Currently, I use this code, and when it gets to the number of copies I want, I just hit escape to finish running the macro. This code works great - does anyone know how to keep the basic idea of this code but add an input box that asks me how many copies I want?

 Sub CopyMultipleSheets()
For i = 1 To 30
Sheets("Planner").Copy after:=Sheets(Worksheets.Count)
Next i
End Sub

Theresag · Feb 17, 2012

Check this link. It is on this site, and works beautifully.

http://www.mrexcel.com/forum/showthread.php?t=22989

flyinghigher13 · Jul 27, 2013

Dragracer said:
James I assume you are copying the sheet to the same workbook.

Use this to copy the sheet multiple times. Warning it will copy any macros in the worksheet module too.

Sub Copysheet()

Dim i As Integer
Dim p As Integer
On Error GoTo out
i = InputBox("How many copies do you what?", "Making Copies")

Application.ScreenUpdating = False
p = 0
Do
ActiveSheet.Copy After:=Sheets(Sheets.Count)
p = p + 1
Loop Until p = i
Application.ScreenUpdating = True
Exit Sub
out:
MsgBox "copy was cancelled"
Application.ScreenUpdating = True
End Sub

</pre>

[SIZE=-1][ This Message was edited by: Dragracer on 2002-10-02 14:41 ][/SIZE]

Thank you very much just what i was looking for!!

rahulkcjain · Aug 17, 2013

Hi,

The only problem with this code is that it generates sheets with names as "Sheet 1 (2)", "Sheet 1 (3)", "Sheet 1 (4)".

Is there a possible way to the name sheets in this sequence : Sheet1, Sheet2, Sheet3... .... Sheet100.

Thanks in advance.

almouchie · Aug 29, 2015

hello
just wanted to say
thanks I just used the code & it works great
many thanks

twinklingstars · Feb 27, 2016

Dragracer said:
James I assume you are copying the sheet to the same workbook.

Use this to copy the sheet multiple times. Warning it will copy any macros in the worksheet module too.

Sub Copysheet()

Dim i As Integer

Dim p As Integer

On Error GoTo out

i = InputBox("How many copies do you what?", "Making Copies")

Application.ScreenUpdating = False

p = 0

Do

ActiveSheet.Copy After:=Sheets(Sheets.Count)

p = p + 1

Loop Until p = i

Application.ScreenUpdating = True

Exit Sub

out:

MsgBox "copy was cancelled"

Application.ScreenUpdating = True

End Sub

[SIZE=-1][ This Message was edited by: Dragracer on 2002-10-02 14:41 ][/SIZE]

Dear Dragracer,
Thanks for the above codes. I was able to copy active sheet multiple times, but why the formula is not changing for new sheet unlike when we manually copy by highlighting many sheets, and copy them in the same workbook, the formula gets appropriately changed to the newly created sheets except the first (new) sheet. Can you please write special macro for that? Many thanks.

_SkyKing · May 18, 2016

Dragracer said:
James I assume you are copying the sheet to the same workbook.

Use this to copy the sheet multiple times. Warning it will copy any macros in the worksheet module too.
Sub Copysheet()
Dim i As Integer
Dim p As Integer
On Error GoTo out
i = InputBox("How many copies do you what?", "Making Copies")
Application.ScreenUpdating = False
p = 0
Do
ActiveSheet.Copy After:=Sheets(Sheets.Count)
p = p + 1
Loop Until p = i
Application.ScreenUpdating = True
Exit Sub
out:
MsgBox "copy was cancelled"
Application.ScreenUpdating = True
End Sub[SIZE=-1][ This Message was edited by: Dragracer on 2002-10-02 14:41 ][/SIZE]

This is fantastic! Thank you!

Copy One Sheet Multiple Times

JamesKM

Board Regular

brownbear2006

New Member

Excel Facts

brownbear2006

New Member

Theresag

New Member

flyinghigher13

New Member

rahulkcjain

New Member

almouchie

Board Regular

twinklingstars

New Member

_SkyKing

New Member

Similar threads

Forum statistics

Share this page

Copy One Sheet Multiple Times

Board Regular

New Member

Excel Facts

New Member

New Member

New Member

New Member

Board Regular

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock