I was given a code to delete sheets 2-31 in a worksheet and then recreate theam based on a modified sheet 1. The delete part works fine but the recreate code gets stuck on sheet 28. It errors on the the following line saying 1004 copy method of worksheet class failed.
Sheets("1").Copy After:=ActiveSheet
If i open the file i can add the sheet by hand (right click, copy/add sheet etc). If i then run the delete and then the recreate code it errors after sheet 10.
If open the original file, run the delete code, then the create code i get 28 sheets, then i rerun the delete code, then i rerun the create code it creates no sheets and gives the same message straight away.
Any idea how i can stop this?
Code below Thanks LiAD
Sub CreateSheets()
Sheets("1").Select Application.DisplayAlerts = False On Error Resume Next For x = 2 To 31 Sheets("1").Copy After:=ActiveSheet Sheets("1 (2)").Name = CStr(x) Next
> I was given a code to delete sheets 2-31 in a worksheet and then recreate > theam based on a modified sheet 1. The delete part works fine but the > recreate code gets stuck on sheet 28. It errors on the the following line > saying 1004 copy method of worksheet class failed.
> Sheets("1").Copy After:=ActiveSheet
> If i open the file i can add the sheet by hand (right click, copy/add > sheet > etc). > If i then run the delete and then the recreate code it errors after sheet > 10.
> If open the original file, run the delete code, then the create code i get > 28 sheets, then i rerun the delete code, then i rerun the create code it > creates no sheets and gives the same message straight away.
> Any idea how i can stop this?
> Code below > Thanks > LiAD
> Sub CreateSheets()
> Sheets("1").Select > Application.DisplayAlerts = False > On Error Resume Next > For x = 2 To 31 > Sheets("1").Copy After:=ActiveSheet > Sheets("1 (2)").Name = CStr(x) > Next
> I was given a code to delete sheets 2-31 in a worksheet and then recreate > theam based on a modified sheet 1. The delete part works fine but the > recreate code gets stuck on sheet 28. It errors on the the following line > saying 1004 copy method of worksheet class failed.
> Sheets("1").Copy After:=ActiveSheet
> If i open the file i can add the sheet by hand (right click, copy/add sheet > etc). > If i then run the delete and then the recreate code it errors after sheet 10.
> If open the original file, run the delete code, then the create code i get > 28 sheets, then i rerun the delete code, then i rerun the create code it > creates no sheets and gives the same message straight away.
> Any idea how i can stop this?
> Code below > Thanks > LiAD
> Sub CreateSheets()
> Sheets("1").Select > Application.DisplayAlerts = False > On Error Resume Next > For x = 2 To 31 > Sheets("1").Copy After:=ActiveSheet > Sheets("1 (2)").Name = CStr(x) > Next
What are your sheet names? Do you have a sheet with the tab as "1", if not you will get an error.
Usually Sheets(1) referes to the 1st tab in the workbook. Sheets("1") refers to a sheet name with the Tab being the number one. Make sure thtere arren't any spaces in the tab on the worksheet. If the sheet name is incorrect you would get an Run Error 9.
Jacob offers what probably works for your situation, seems reasonable upon review. As to why you can't figure out what was going on with your code, I think it's because you have a few items that prevent displaying what you are looking for. :)
The displayalerts = false I think disables your error messages, and if not that then your on error resume next certainly takes you to the next line of code until the system basically can't do anything else.
Hmmm, you may also want to try changing the copy line to read: ws.Copy After:=Sheets(Cstr(x-1))
Not sure how the code responds if you have created say 31 sheets, then if you created a sheet that was manually inserted before sheet 1. If you then ran your delete and create sheets in series it may add sheets 2-31 after the newly created sheet and before sheet "1".
As for 31 sheets and recreating them from sheet 1, almost sounds like you are working with something calendar related. If so you can use other variables to control your upper limit to the number of days in the month in question. Whatever the case, good luck. Almost seems like you are there.
> Sub CreateSheets() > Dim ws As Worksheet > Set ws = Sheets("1")
> For x = 2 To 31 > ws.Copy After:=Sheets(Sheets.Count) > ActiveSheet.Name = CStr(x) > Next
> End Sub
> If this post helps click Yes > --------------- > Jacob Skaria
> "LiAD" wrote:
> > Morning,
> > I was given a code to delete sheets 2-31 in a worksheet and then recreate > > theam based on a modified sheet 1. The delete part works fine but the > > recreate code gets stuck on sheet 28. It errors on the the following line > > saying 1004 copy method of worksheet class failed.
> > Sheets("1").Copy After:=ActiveSheet
> > If i open the file i can add the sheet by hand (right click, copy/add sheet > > etc). > > If i then run the delete and then the recreate code it errors after sheet 10.
> > If open the original file, run the delete code, then the create code i get > > 28 sheets, then i rerun the delete code, then i rerun the create code it > > creates no sheets and gives the same message straight away.
> > Any idea how i can stop this?
> > Code below > > Thanks > > LiAD
> > Sub CreateSheets()
> > Sheets("1").Select > > Application.DisplayAlerts = False > > On Error Resume Next > > For x = 2 To 31 > > Sheets("1").Copy After:=ActiveSheet > > Sheets("1 (2)").Name = CStr(x) > > Next
I can delete ok still but i cannot recreate all the sheets.
If I
- run the delete then the recreate i can create 19 sheets - run the delete macro, save the file as a different name, run the create macro i get 19 sheets. - run the delete macro, save the file as a different name, close the file, open the new file and run the create macro i get 27 sheets - almost there!!
The same thing happens above in using the line - ws.Copy After:=Sheets(Cstr(x-1)) suggested by GB.
So there is something that is changing how the macro operates. During the time i run this i do nothing else other than open the file, run macros and close as described above. At the same time i have other applications open (no other excel or word though).
> Sub CreateSheets() > Dim ws As Worksheet > Set ws = Sheets("1")
> For x = 2 To 31 > ws.Copy After:=Sheets(Sheets.Count) > ActiveSheet.Name = CStr(x) > Next
> End Sub
> If this post helps click Yes > --------------- > Jacob Skaria
> "LiAD" wrote:
> > Morning,
> > I was given a code to delete sheets 2-31 in a worksheet and then recreate > > theam based on a modified sheet 1. The delete part works fine but the > > recreate code gets stuck on sheet 28. It errors on the the following line > > saying 1004 copy method of worksheet class failed.
> > Sheets("1").Copy After:=ActiveSheet
> > If i open the file i can add the sheet by hand (right click, copy/add sheet > > etc). > > If i then run the delete and then the recreate code it errors after sheet 10.
> > If open the original file, run the delete code, then the create code i get > > 28 sheets, then i rerun the delete code, then i rerun the create code it > > creates no sheets and gives the same message straight away.
> > Any idea how i can stop this?
> > Code below > > Thanks > > LiAD
> > Sub CreateSheets()
> > Sheets("1").Select > > Application.DisplayAlerts = False > > On Error Resume Next > > For x = 2 To 31 > > Sheets("1").Copy After:=ActiveSheet > > Sheets("1 (2)").Name = CStr(x) > > Next
I have (or had) one workbook open. It starts with either 33 sheets (which then got deleted with the code and sheet 1 altered before running the create code), or 3 sheets (file saved after the delete code had ran).
> > I was given a code to delete sheets 2-31 in a worksheet and then recreate > > theam based on a modified sheet 1. The delete part works fine but the > > recreate code gets stuck on sheet 28. It errors on the the following line > > saying 1004 copy method of worksheet class failed.
> > Sheets("1").Copy After:=ActiveSheet
> > If i open the file i can add the sheet by hand (right click, copy/add > > sheet > > etc). > > If i then run the delete and then the recreate code it errors after sheet > > 10.
> > If open the original file, run the delete code, then the create code i get > > 28 sheets, then i rerun the delete code, then i rerun the create code it > > creates no sheets and gives the same message straight away.
> > Any idea how i can stop this?
> > Code below > > Thanks > > LiAD
> > Sub CreateSheets()
> > Sheets("1").Select > > Application.DisplayAlerts = False > > On Error Resume Next > > For x = 2 To 31 > > Sheets("1").Copy After:=ActiveSheet > > Sheets("1 (2)").Name = CStr(x) > > Next
The sheet names are correct without spaces etc ( if they were not right it would not create any sheets surely, not 27 then stop?).
Is there something in memeory that could mean it bums out? The reason I ask is that it starts creating quite fast then slows down the more sheets it creates.
> What are your sheet names? Do you have a sheet with the tab as "1", if > not you will get an error.
> Usually Sheets(1) referes to the 1st tab in the workbook. Sheets("1") > refers to a sheet name with the Tab being the number one. Make sure > thtere arren't any spaces in the tab on the worksheet. If the sheet > name is incorrect you would get an Run Error 9.
|
