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
method.

Has anyone found a work around?????

Jerid

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

Dave
OzGrid Business Applications

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

Dave

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.