MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel97 Copy Method

Posted by Jerid on April 20, 2001 6:24 AM

I'm trying to copy a multiple sheets with charts into a new workbook. I can copy
about 40 sheets before I get an access violation error.

Microsoft has confirmed a problem with the Copy Method and claims that it's fixed
in 2000, but that doesn't help me because I'm using 97.
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

I have tried all of the following to find a work around 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

Has anyone found a work around?????


Posted by Dave Hawley on April 20, 2001 6:46 AM

Hi Jerid

I d remember something about this, i think it has something to do with Excel creating a new CodeName for each Sheet that is copied and the number becomes to large. I have Excel 2000 so i cannot test this but try this.

Sub Maybe()
Dim OallShts As Object
Dim i As Integer

For i = 1 To ThisWorkbook.Sheets.Count
Set OallShts = ThisWorkbook.Sheets(i)
'Your copy code
Set OallShts = Nothing
Next i

End Sub

OzGrid Business Applications

Posted by Jerid on April 20, 2001 12:16 PM


Thanks Dave, but that didn't work either.

Everything works when the sheets just have data in them, but not with the charts.
I think I'm giving up, tough for me to say, but I can't seem to find a work around. It looks like I need to spend the time to create the charts dynamically instead of having templates.

Oh well thanks anyway.