The object invoked has disconnected from its clinets

[Don]

Hi,

I am copying sheets in a macro...
Copy a sheet, rename the copy, copy the sheet again, and
rename the copy again...

Usually on the third copy,I get the following error
message:

Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients

If I then try to do a manual copy, Excel crashes and sends
a Dump to Microsoft.

If I start again and do the copies manually, then on the
third time, Excel crashes and sends a Dump to Microsoft.

I was thinking I was running out of resources, so I
deleted a large number of sheets and reduced the size of
the workbook by 60%, but the problem still occurs evrery
time.


Any ideas gratefuly received.

Don

 

[Dave Peterson]

If you create a new test version of your workbook (with just a little data in
each sheet so the macro can run), does it work ok?

I had one workbook that had one worksheet that was corrupted. I couldn't copy
it to another workbook via code or manually.

Interestingly, I could move the sheet without the crash. But that scared me.
(How long before Move wouldn't work.) So I did a little testing to make sure my
code wasn't the problem. When I was convinced that it wasn't the code, I
recreated that problem worksheet and it's been fine ever since.

 

[Dave Peterson]

Just curious if you tried your theory against a fresh workbook?

This worked ok for me:

Option Explicit
Sub testme01()

Dim newWks As Worksheet
Dim myPict As Picture
Dim iCtr As Long

Set newWks = Worksheets.Add

With newWks
Set myPict = .Pictures.Insert("C:\MyPicture.gif")
End With

With myPict
.OnAction = ThisWorkbook.Name & "!hithere"
End With

For iCtr = 1 To 100
newWks.Copy _
before:=newWks
Next iCtr

End Sub

Sub hithere()
MsgBox "hi there from " & Application.Caller
End Sub