I have heard of this before, but I cannot remember the reason. Try this method, it works for me.
Dim Sht As Worksheet, i As Integer
Application.ScreenUpdating = False
For i = 1 To 20
Set Sht = Sheets("Sheet2")
Set Sht = Nothing
Application.ScreenUpdating = True
OzGrid Business Applications
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.