Copy One Sheet Multiple Times

[JamesKM]

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

 

[Dragracer]

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.<pre>

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>
This message was edited by Dragracer on 2002-10-02 14:41

 

[JamesKM]

Thanks for the macro. It works great when put into the specific workbook module, but does not seem to be on offer when in personal.xls - is there a way round this?

James

 

[Dragracer]

James, don't know why it would not be active when in your personal.xls, It is for me. Make sure it is in a general module and not a sheet module.

I also modified the code a little, p's original value should have been 0 and I added some error handling in case you hit cancel.

 

[noppojp]

VERY HAPPY MAN Here.
The above code saved me hours i think.
it was posted here 5 years ago, and i'm just now seeing it, but i'm grateful still the same.

LOVE this site folks. Give it up for Dragracer!! Thanks Brah!!

 

[samialn]

my god, i love this site, i am deleting my post since i found my answer here!!!

 

[Sukesh007]

Hi Thank you for the macro,
i had another question though, what if i want to sequntially rename worksheets along with the macro

 

[samialn]

i'll add to that by asking, how do i get formulas to recognize the new sheets?

 

[Papi]

Hello Sukesh007,

In your response to auto numbering worksheets, I tripped on this one today in error. If you number your first worksheet (1) using parenthesis on both sides then each worksheet will follow the same pattern without telling you that it is a copy of the first one. Sheet 2 will be (2), sheet 3 will be (3) etc.

Hope this helps.

 

[Theresag]

This site has saved me HOURS of my life, and from HOURS of frustration. Amazing site indeed, and brilliant helpful people. Thanks again, Mr. Excle!