I have a workbook containing 5 sheets. I’m trying to make 50 copies of
"sheet1" but i get a error message after 40 copies are created saying "Copy
method of Worksheet failed". I have 2 formulas and some text in the worksheet
I’m trying to copy. If I remove my formulas I can make 47 copies before I get
the same error. If I try this on a blank worksheet it works fine. Any help on
this is appreciated. Sample of my code below....


Sub test()
Dim i As Integer
i = 0

While i < 50

Worksheets("sheet1").Copy After:=Worksheets("sheet1")
i = i + 1
Wend

End Sub

The maximum number of worksheets is limited by memory and you seem to have
hit a brick wall at 40ish which is very low. I suspect you need more memory
in your PC or less applications running.

Mike

"Mr Exxxel" wrote:

> I have a workbook containing 5 sheets. I’m trying to make 50 copies of
> "sheet1" but i get a error message after 40 copies are created saying "Copy
> method of Worksheet failed". I have 2 formulas and some text in the worksheet
> I’m trying to copy. If I remove my formulas I can make 47 copies before I get
> the same error. If I try this on a blank worksheet it works fine. Any help on
> this is appreciated. Sample of my code below....
>
>
> Sub test()
> Dim i As Integer
> i = 0
>
> While i < 50
>
> Worksheets("sheet1").Copy After:=Worksheets("sheet1")
> i = i + 1
> Wend
>
> End Sub

This seems to be false error in that it's not really caused by a lack of
memory, but the fact of copying in a loop and some bug in Excel.
The only solution I have seen is limit the loop to number you know is always
OK (say 20), Save (and possibly .Close) the WB, then copy again.

Or maybe adding WS's (from a template) instead of copying will work for you.

NickHK

"Mr Exxxel" <Mr (E-Mail Removed)> wrote in message
news:1CBD1C62-E394-4C9F-BAD3-(E-Mail Removed)...
> I have a workbook containing 5 sheets. I'm trying to make 50 copies of
> "sheet1" but i get a error message after 40 copies are created saying
"Copy
> method of Worksheet failed". I have 2 formulas and some text in the
worksheet
> I'm trying to copy. If I remove my formulas I can make 47 copies before I
get
> the same error. If I try this on a blank worksheet it works fine. Any help
on
> this is appreciated. Sample of my code below....
>
>
> Sub test()
> Dim i As Integer
> i = 0
>
> While i < 50
>
> Worksheets("sheet1").Copy After:=Worksheets("sheet1")
> i = i + 1
> Wend
>
> End Sub

Hmm ok, thx for the help.
How do I make a template and create WS from it?


"NickHK" wrote:

> This seems to be false error in that it's not really caused by a lack of
> memory, but the fact of copying in a loop and some bug in Excel.
> The only solution I have seen is limit the loop to number you know is always
> OK (say 20), Save (and possibly .Close) the WB, then copy again.
>
> Or maybe adding WS's (from a template) instead of copying will work for you.
>
> NickHK
>
> "Mr Exxxel" <Mr (E-Mail Removed)> wrote in message
> news:1CBD1C62-E394-4C9F-BAD3-(E-Mail Removed)...
> > I have a workbook containing 5 sheets. I'm trying to make 50 copies of
> > "sheet1" but i get a error message after 40 copies are created saying
> "Copy
> > method of Worksheet failed". I have 2 formulas and some text in the
> worksheet
> > I'm trying to copy. If I remove my formulas I can make 47 copies before I
> get
> > the same error. If I try this on a blank worksheet it works fine. Any help
> on
> > this is appreciated. Sample of my code below....
> >
> >
> > Sub test()
> > Dim i As Integer
> > i = 0
> >
> > While i < 50
> >
> > Worksheets("sheet1").Copy After:=Worksheets("sheet1")
> > i = i + 1
> > Wend
> >
> > End Sub
>
>
>