Copy method of Worksheet class failed

[smelba]

I have an Excel workbook with VBA code that makes several copies of one of the sheets. Every time I run the macro, I get the following error:

Run-time error '1004':
Copy method of Worksheet class failed

The error always occurs when there are 58 sheets in the workbook and the macro is attempting to create the 59th. My code is as follows:

For i = 1 To {variable} Step 1
Sheets({sheetname}).Copy After:=Sheets(Sheets.Count)
Next i

I am running Excel 2000 and VB 6.0 in Win 2000.

Any help will be greatly appreciated!

 

[Zathras]

You're going to have to post more of your code. I just ran the macro below twice and now have 141 sheets in my workbook with no problem.
[blue]

Option Explicit
Sub test()
Dim i As Integer

  For i = 1 To 70 Step 1
    Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
  Next i
End Sub

[/color]
AFAIK there is nothing special about 59 sheets. Perhaps you are running out of memory?

 

[SkipVought]

My guess is that the sheetname that you are using is not a valid name.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

zathras,

I don't know what time zone you are in -- it's 6:41 PM here in Texas...

Maybe we ought to get a life! HA! ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smelba]

Zathras and Skip,

I figured it out. For some strange reason, when the original sheet that's being copied has "Rows to repeat at the top" setup in Page Setup, the macro will only copy it a limited amount of times. When I clear the "Rows to repeat at the top" field, it makes as many copies as I want.

Thanks for your input and advice.