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

It creates all 31 for me.

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

--
__________________________________
HTH

Bob

"LiAD" <(E-Mail Removed)> wrote in message
news:80645942-297A-4073-90F8-(E-Mail Removed)...
> 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
>
>

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
---------------
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
>
> End Sub
>
>

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.


"Jacob Skaria" wrote:

> 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
> ---------------
> 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
> >
> > End Sub
> >
> >

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?

"Jacob Skaria" wrote:

> 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
> ---------------
> 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
> >
> > End Sub
> >
> >

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

"Bob Phillips" wrote:

> It creates all 31 for me.
>
> How many workbooks are open, how many sheets does it already have?
>
> --
> __________________________________
> HTH
>
> Bob
>
> "LiAD" <(E-Mail Removed)> wrote in message
> news:80645942-297A-4073-90F8-(E-Mail Removed)...
> > 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
> >
> >
>
>
> .
>

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.

"joel" wrote:

>
> 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.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=152282
>
> Microsoft Office Help
>
> .
>