Code inconsistent

[LiAD]

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

End Sub

 

[Bob Phillips]

It creates all 31 for me.

How many workbooks are open, how many sheets does it already have?

 

[Jacob Skaria]

Try this on activesheet

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

 

[GB]

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.

 

[LiAD]

OK so heres the bad news

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

Any other ideas?

 

[LiAD]

Hi,

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

 

[LiAD]

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.