I have a workbook containing 5 sheets. Im 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
Im 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. Im 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
Im 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 wrote in message
...
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 wrote in message
...
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