Worksheets.Copy meathod fails after calling number of times

Posted by Sam Spackman on March 29, 2001 1:01 PM

Has anyone ever run into this? I have code which copies a worksheet multiple times after another sheet in the same workbook (renaming it something new after the copy). After running this macro 6 times (and it copies the sheet 4 times per run) it crashes with a 1004 error and says copy method of worksheets failed. Indeed, I can't even copy a worksheet if I terminate the procedure and just go into Excel. Has anyone ever seen this?

--Sam

Posted by Dave Hawley on March 29, 2001 5:58 PM

Hi Sam

I have heard of this before, but I cannot remember the reason. Try this method, it works for me.

Sub CopySheet()
Dim Sht As Worksheet, i As Integer
Application.ScreenUpdating = False
For i = 1 To 20
Set Sht = Sheets("Sheet2")
Sht.Copy After:=Sheets(2)

Next
Set Sht = Nothing
Application.ScreenUpdating = True

End Sub

Dave

OzGrid Business Applications

Posted by Jerid Ahrens on April 12, 2001 8:56 AM

I have the same problem, only I'm coping a sheet with charts on it to a new workbook. I can copy about 40 sheets before I get the error.

Microsoft has confirmed this problem and claims that it's fixed in 2000, but that doesn't help me.
Possible Errors messages you could receive are -
Win95 - invalid page fault in MSO97.DLL or Excel.exe
NT - Excel.exe Exception access violation (My error) or Run-Time error 1004 (Sam's error)

Dave, I took your advise and changed my code to be similar to what you recommended above, and I still have the problem.

In fact I have tried all of the following with no luck
1) API call to empty the clipboard after each copy
2) API call to sleep for 1 sec after each copy with a DoEvents
3) Changed the code from (Sheet.copy After Workbook.sheet) to (Add new sheet, then copy and paste cells), this works but my charts don't copy with this method.

I'm just hoping that someone found a work around for this problem.

Thanks for any advise anyone may have.