Why is my Worksheet.copy method failing?

[ERE]

I am writing a VBA application in Excel to analize data from some testing. I have a formatted template worksheet that needs to be copied for each line of data read into the workbook. Everything works great until the 33 pass through the copy command at which point I get the error message:

Run-Time Error '1004'
Copy method of Worksheet class failed

The command that I think is failing is:

Worksheets("Template1").Copy before:=Worksheets(1)

This command copies the Template1 sheet and places the copy at the beginning of the worksheets list.

Any thoughts on why the method is failing after running smoothly 32 times?

Ed

 

[handleman]

Out of memory?

 

[ERE]

I don't think it is a memory issue. I'm wondering if it is a bug in the software. If I insert a new worksheet for each data record instead of copying a template, the program runs fine. I now have to format each sheet as it's inserted, which just takes longer to run. Thanks.

Ed

 

[joerd]

It works fine 40 times (or more) here, so maybe it is in some other part of your code?

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ERE]

There isn't anything else in the code that looks suspicious to me. I've rewritten portions of the code to insert a blank worksheet and format it appropriately. There were some issues with named cells that I've taken care of, so I think the problem as been solved.

Thanks for your suggestions.

Ed