Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet and rename
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
Copy/Rename a sheet | Links and Linking in Excel | |||
Button to copy sheet, rename sheet sequencially. | Excel Programming | |||
Copy a sheet and rename it | Excel Programming | |||
copy Sheet and rename it! | Excel Programming |