Copy One Sheet Multiple Times

JamesKM

Board Regular
Joined
Aug 12, 2002
Messages
130
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Dragracer

Board Regular
Joined
Jun 9, 2002
Messages
151
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

Board Regular
Joined
Aug 12, 2002
Messages
130
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

Board Regular
Joined
Jun 9, 2002
Messages
151
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

Board Regular
Joined
Nov 4, 2005
Messages
69

ADVERTISEMENT

:biggrin: VERY HAPPY MAN Here. :biggrin:
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

New Member
Joined
Sep 24, 2007
Messages
11
my god, i love this site, i am deleting my post since i found my answer here!!!
 

Sukesh007

New Member
Joined
Jan 8, 2005
Messages
1

ADVERTISEMENT

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

samialn

New Member
Joined
Sep 24, 2007
Messages
11
i'll add to that by asking, how do i get formulas to recognize the new sheets?
 

Papi

Well-known Member
Joined
May 22, 2007
Messages
1,592
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

New Member
Joined
Mar 6, 2007
Messages
29
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!
 

Forum statistics

Threads
1,144,148
Messages
5,722,791
Members
422,458
Latest member
Muirzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top