Multiple copies of a worksheet

S

SirSFZ

I am creating a workbook with up to 30 sheets. I have created a templat
in sheet 2, and want to copy that sheet 29 times for multiple users t
them enter data into. It is rather burdensome to keep selecting an
copying (w/ move to end) sheets until I have the amount I need.

Is there a way to create multiple copies at one time? Any help i
appreciated
 
D

Don Guillett

try this
Sub copytemplate()
For i = 1 To 29 'or InputBox("how many")
Sheets("first").Copy after:=Sheets(Worksheets.Count)
Next i
End Sub
 
M

Mel

Humm, not sure what you mean, but you can select say what you want to copy,
then go to the tabs at the bottom and select the first page you want to copy
to and hold down shift and hit the last page you want to copy to and then
select one cell in the first page and hit "enter", that's how I do it anyway
.....I'm no expert and someone else might have a better way.
Mel
 
G

Graham Whitehead

Hi, this should do the job for you:

Sub create_copies_of_page_2()

Dim x As Integer

For x = 1 To 39 '(will create 39 copies)
'copy sheet two
Sheets("Sheet2").Copy after:=Sheets(3)
'rename the new sheet
Sheets("Sheet2 (2)").Name = "Copy " & x
Next x

End Sub


Just paste this into a new module, make the neccesary adjustements, i.e. if
the sheet you want to copy is not called 'Sheet2' then insert the name of
your sheet here etc.
 
G

Gord Dibben

Manually copy first sheet.

SHIFT + Click to select both sheets and copy.

Select 4 sheets and copy.

Select 8 sheets and copy.

Select 16 sheets and copy.

Not too many steps doing it this way.

If you want to use VBA.........................

Sub SheetCopy()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times")
For i = 1 To shts
ActiveSheet.Copy after:=ActiveSheet
Next i
Application.ScreenUpdating = True
endit:
End Sub

Then you're going to want to re-name the 29 sheets, right?

In original sheet insert a new column temporarily to the left of Column A.

Enter 30 unique names in A1:A30

Run this macro to rename all 30 sheets.

Sub NameWS()
For i = 1 To 30
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next
End Sub

When sheets are re-named, delete the temporary column in original sheet.


Gord Dibben MS Excel MVP
 
D

Don Guillett

to name the sheets 1,2,3,4,etc
Sub copytemplate()
For i = 1 To 29 'or InputBox("how many")
Sheets("first").Copy after:=Sheets(Worksheets.Count) activesheet.name="sh " & i
Next i
End Sub
Click to expand...
 
Top