Multiple copies of a worksheet

[SirSFZ]

I am creating a workbook with up to 30 sheets. I have created a template in sheet 2, and want to copy that sheet 29 times for multiple users to them enter data into. It is rather burdensome to keep selecting and 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 is appreciated.

[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

"SirSFZ" <[email protected]> wrote in
message news:[email protected]...
>
> I am creating a workbook with up to 30 sheets. I have created a template
> in sheet 2, and want to copy that sheet 29 times for multiple users to
> them enter data into. It is rather burdensome to keep selecting and
> 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 is
> appreciated.
>
>
> --
> SirSFZ
> ------------------------------------------------------------------------
> SirSFZ's Profile:
> http://www.excelforum.com/member.php...o&userid=21682
> View this thread: http://www.excelforum.com/showthread...hreadid=558199
>

[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

--
Don Guillett
SalesAid Software
[email protected]
"SirSFZ" <[email protected]> wrote in
message news:[email protected]...
>
> I am creating a workbook with up to 30 sheets. I have created a template
> in sheet 2, and want to copy that sheet 29 times for multiple users to
> them enter data into. It is rather burdensome to keep selecting and
> 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 is
> appreciated.
>
>
> --
> SirSFZ
> ------------------------------------------------------------------------
> SirSFZ's Profile:
> http://www.excelforum.com/member.php...o&userid=21682
> View this thread: http://www.excelforum.com/showthread...hreadid=558199
>

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

"SirSFZ" <[email protected]> wrote in
message news:[email protected]...
>
> I am creating a workbook with up to 30 sheets. I have created a template
> in sheet 2, and want to copy that sheet 29 times for multiple users to
> them enter data into. It is rather burdensome to keep selecting and
> 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 is
> appreciated.
>
>
> --
> SirSFZ
> ------------------------------------------------------------------------
> SirSFZ's Profile:
> http://www.excelforum.com/member.php...o&userid=21682
> View this thread: http://www.excelforum.com/showthread...hreadid=558199
>

[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

On Tue, 4 Jul 2006 09:51:17 -0500, SirSFZ
<[email protected]> wrote:

>
>I am creating a workbook with up to 30 sheets. I have created a template
>in sheet 2, and want to copy that sheet 29 times for multiple users to
>them enter data into. It is rather burdensome to keep selecting and
>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 is
>appreciated.

[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


--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> try this
> Sub copytemplate()
> For i = 1 To 29 'or InputBox("how many")
> Sheets("first").Copy after:=Sheets(Worksheets.Count)
> Next i
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "SirSFZ" <[email protected]> wrote in
> message news:[email protected]...
>>
>> I am creating a workbook with up to 30 sheets. I have created a template
>> in sheet 2, and want to copy that sheet 29 times for multiple users to
>> them enter data into. It is rather burdensome to keep selecting and
>> 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 is
>> appreciated.
>>
>>
>> --
>> SirSFZ
>> ------------------------------------------------------------------------
>> SirSFZ's Profile:
>> http://www.excelforum.com/member.php...o&userid=21682
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=558199
>>
>
>

[KARENKERBA]

This worked like a charm for me!! Thank you.