You could always just clear the already created 1-31 sheets, as desired.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"LiAD" <(E-Mail Removed)> wrote in message
news:1596B86D-45A5-4C19-8762-(E-Mail Removed)...
> 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
Similar Threads
