Excel Copy Method Failing.

Hello I have a script that loops through copying sheets as below, it
imports 7 sheets then goes through this loop:

Worksheets(7).Select
Worksheets(7).Copy After:=Sheets(7)
Worksheets(8).Name = szUserid

This basically loops through and creates identical sheets that are then
populated with data. All in all it creates about 40-50 sheets. All with
different names.

Te first time I run it it works fine. Then after that it comes up with
a error:

Runtime Error 1004
Copy Method of Worksheet Clase Failed.

I have restarted Excel and it still wont run, then restarted the PC and
it still wont run. I then close excel wait a while and try it again and
it runs. Nothing has changed. Very weird. The files I am asking it to
create exist and the method works most of the time so is it a memory
problem, but restarting the PC should solve this.

Any ideas gratefully received.

Thanks John

Hi John,

I found these useful links while searching your problem on Google


http://support.microsoft.com/?Â*kbid=210684
Xl2000: Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/?Â*kbid=177634
Xl97: Copy Method Of Sheets Object Causes Invalid Page Fault

See if they help.

Alok Joshi

"[email protected]" wrote:

> Hello I have a script that loops through copying sheets as below, it
> imports 7 sheets then goes through this loop:
>
> Worksheets(7).Select
> Worksheets(7).Copy After:=Sheets(7)
> Worksheets(8).Name = szUserid
>
> This basically loops through and creates identical sheets that are then
> populated with data. All in all it creates about 40-50 sheets. All with
> different names.
>
> Te first time I run it it works fine. Then after that it comes up with
> a error:
>
> Runtime Error 1004
> Copy Method of Worksheet Clase Failed.
>
> I have restarted Excel and it still wont run, then restarted the PC and
> it still wont run. I then close excel wait a while and try it again and
> it runs. Nothing has changed. Very weird. The files I am asking it to
> create exist and the method works most of the time so is it a memory
> problem, but restarting the PC should solve this.
>
> Any ideas gratefully received.
>
> Thanks John
>
>

This worked a treat thanks. How come I never find anything on the
microsoft website. Thanks for this the first one was the problem.

Cheers John