Copy sheet and rename

[LiAD]

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

 

[Mike H]

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]

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]

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]

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.

 

[Don Guillett]

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