Copy sheet and rename
 

Hi,

I have a file with a series of worksheet tabs, called Data, Analysis,
Results and 1. I would like to make 30 copies of the sheet named 1 and
rename them 2, 3 etc in a sequence (1 for each day of the month).

There may be any number of sheets in the worksheet before the copy is made.
I always need to have from sheets 1 to 31.

What macro's could i use to-:
Delete sheets 2-31 in the existing sheet
Copy sheet 1 30 times, rename them and put them in numerical order.

The reason to delete and then recopy is it allows me to change once then
copy as all will be the same.

Thanks

Copy sheet and rename
 

Try this

Sub Sonic()
Sheets("1").Select
Application.DisplayAlerts = False
On Error Resume Next
For x = 2 To 31
Sheets(CStr(x)).Delete
Next
Application.DisplayAlerts = True

For x = 2 To 31
Sheets("1").Copy After:=ActiveSheet
Sheets("1 (2)").Name = CStr(x)
Next
End Sub

Mike

"LiAD" wrote:

Hi,

I have a file with a series of worksheet tabs, called Data, Analysis,
Results and 1. I would like to make 30 copies of the sheet named 1 and
rename them 2, 3 etc in a sequence (1 for each day of the month).

There may be any number of sheets in the worksheet before the copy is made.
I always need to have from sheets 1 to 31.

What macro's could i use to-:
Delete sheets 2-31 in the existing sheet
Copy sheet 1 30 times, rename them and put them in numerical order.

The reason to delete and then recopy is it allows me to change once then
copy as all will be the same.

Thanks

Copy sheet and rename
 

Thanks,

The delete bit works fine.

However the recreate part gets jammed on sheet 28 - it creates sheet 28 then
throws up an error message of copy method of worksheet class failed, the line
that error's is

Sheets("1").Copy After:=ActiveSheet

any ideas on how to get round this?
Thanks

"Mike H" wrote:

Try this

Sub Sonic()
Sheets("1").Select
Application.DisplayAlerts = False
On Error Resume Next
For x = 2 To 31
Sheets(CStr(x)).Delete
Next
Application.DisplayAlerts = True

For x = 2 To 31
Sheets("1").Copy After:=ActiveSheet
Sheets("1 (2)").Name = CStr(x)
Next
End Sub

Mike

"LiAD" wrote:

Hi,

I have a file with a series of worksheet tabs, called Data, Analysis,
Results and 1. I would like to make 30 copies of the sheet named 1 and
rename them 2, 3 etc in a sequence (1 for each day of the month).

There may be any number of sheets in the worksheet before the copy is made.
I always need to have from sheets 1 to 31.

What macro's could i use to-:
Delete sheets 2-31 in the existing sheet
Copy sheet 1 30 times, rename them and put them in numerical order.

The reason to delete and then recopy is it allows me to change once then
copy as all will be the same.

Thanks

Copy sheet and rename
 

It doesn't do that for me in fact I just tested it on 100 sheets and it's
fine. The number of worksheets is limited by system memory, is that your
issue?

"LiAD" wrote:

Thanks,

The delete bit works fine.

However the recreate part gets jammed on sheet 28 - it creates sheet 28 then
throws up an error message of copy method of worksheet class failed, the line
that error's is

Sheets("1").Copy After:=ActiveSheet

any ideas on how to get round this?
Thanks

"Mike H" wrote:

Try this

Sub Sonic()
Sheets("1").Select
Application.DisplayAlerts = False
On Error Resume Next
For x = 2 To 31
Sheets(CStr(x)).Delete
Next
Application.DisplayAlerts = True

For x = 2 To 31
Sheets("1").Copy After:=ActiveSheet
Sheets("1 (2)").Name = CStr(x)
Next
End Sub

Mike

"LiAD" wrote:

Hi,

I have a file with a series of worksheet tabs, called Data, Analysis,
Results and 1. I would like to make 30 copies of the sheet named 1 and
rename them 2, 3 etc in a sequence (1 for each day of the month).

There may be any number of sheets in the worksheet before the copy is made.
I always need to have from sheets 1 to 31.

What macro's could i use to-:
Delete sheets 2-31 in the existing sheet
Copy sheet 1 30 times, rename them and put them in numerical order.

The reason to delete and then recopy is it allows me to change once then
copy as all will be the same.

Thanks

Copy sheet and rename
 

Hi,

How do i check this?

I can add the sheets manually no problem.
If i change the code to stop at sheet 28 it works fine but any number
greater than 28 does not work.

"Mike H" wrote:

It doesn't do that for me in fact I just tested it on 100 sheets and it's
fine. The number of worksheets is limited by system memory, is that your
issue?

"LiAD" wrote:

Thanks,

The delete bit works fine.

However the recreate part gets jammed on sheet 28 - it creates sheet 28 then
throws up an error message of copy method of worksheet class failed, the line
that error's is

Sheets("1").Copy After:=ActiveSheet

any ideas on how to get round this?
Thanks

"Mike H" wrote:

Try this

Sub Sonic()
Sheets("1").Select
Application.DisplayAlerts = False
On Error Resume Next
For x = 2 To 31
Sheets(CStr(x)).Delete
Next
Application.DisplayAlerts = True

For x = 2 To 31
Sheets("1").Copy After:=ActiveSheet
Sheets("1 (2)").Name = CStr(x)
Next
End Sub

Mike

"LiAD" wrote:

Hi,

I have a file with a series of worksheet tabs, called Data, Analysis,
Results and 1. I would like to make 30 copies of the sheet named 1 and
rename them 2, 3 etc in a sequence (1 for each day of the month).

There may be any number of sheets in the worksheet before the copy is made.
I always need to have from sheets 1 to 31.

What macro's could i use to-:
Delete sheets 2-31 in the existing sheet
Copy sheet 1 30 times, rename them and put them in numerical order.

The reason to delete and then recopy is it allows me to change once then
copy as all will be the same.

Thanks

Copy sheet and rename
 

You could always just clear the already created 1-31 sheets, as desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

How do i check this?

I can add the sheets manually no problem.
If i change the code to stop at sheet 28 it works fine but any number
greater than 28 does not work.

"Mike H" wrote:

It doesn't do that for me in fact I just tested it on 100 sheets and it's
fine. The number of worksheets is limited by system memory, is that your
issue?

"LiAD" wrote:

Thanks,

The delete bit works fine.

However the recreate part gets jammed on sheet 28 - it creates sheet 28
then
throws up an error message of copy method of worksheet class failed,
the line
that error's is

Sheets("1").Copy After:=ActiveSheet

any ideas on how to get round this?
Thanks

"Mike H" wrote:

Try this

Sub Sonic()
Sheets("1").Select
Application.DisplayAlerts = False
On Error Resume Next
For x = 2 To 31
Sheets(CStr(x)).Delete
Next
Application.DisplayAlerts = True

For x = 2 To 31
Sheets("1").Copy After:=ActiveSheet
Sheets("1 (2)").Name = CStr(x)
Next
End Sub

Mike

"LiAD" wrote:

Hi,

I have a file with a series of worksheet tabs, called Data,
Analysis,
Results and 1. I would like to make 30 copies of the sheet named 1
and
rename them 2, 3 etc in a sequence (1 for each day of the month).

There may be any number of sheets in the worksheet before the copy
is made.
I always need to have from sheets 1 to 31.

What macro's could i use to-:
Delete sheets 2-31 in the existing sheet
Copy sheet 1 30 times, rename them and put them in numerical order.

The reason to delete and then recopy is it allows me to change once
then
copy as all will be the same.

Thanks