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

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.


Don wrote:

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

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 _
befo=newWks
Next iCtr

End Sub

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


Don wrote:

I have found that the presence of any Picture in the
workbook, with any Macro Assigned to the picture causes
this problem without fail.

I delete the Mactro Assigned Picture, leaving many other
pictures, and the third copy works fine.

Micros**t strikes again!


-----Original Message-----
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.


Don wrote:

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

.


--

Dave Peterson