I have a very simple macro that carries out a simple repetitive task involving copying the highlighted cells (mostly formulae) and pasting back above the highlighted (Effectively the Copy & Insert Copied cells commands). Here's the VBA:
Sub CopyInsert()
' Macro to copy and insert a line or range
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub
Here's the error:
"Run-Time error '-2147417848 (80010108)':
Automation Error
The object invoked has disconnected from its clients." I've tried Debug and it doesn't help - only solution is to kill excel and restart.
I use this macro in a dozen or more spreadsheets on a regular basis and on most it works fine but on some it causes excel to crash (the files are in two types with slight formula differences as to whether it is looking at gas or electricity prices stored in a single external spreadsheet - it's always the gas ones that fail for no obvious reason). In the files where it crashes it seems to cause it only on certain sheets and always on the same ones - so it will work fine on one sheet then moving to another which performs calculations on data in the first and it crashes. When I run the macros in the same file on a colleagues machine (a desktop) it's fine.... So I thought this might be a hardware / memory issue - I've just changed laptops and upgraded the newer laptop from 4GB RAM to 8GB RAM but no improvement. While writing this I've found that in a file it previously errored it didn't when I just tried it so it is clearly an intermittent problem and may be related to what else happens to be running on the machine at the time. The spreadsheet I'm running the macro on isn't small (around 10MB) as is the file containing the raw data. It doesn't seem to matter whether I have the raw data file open or not except that the links are updated quicker when it's open obviously.
Any suggestions on what might be causing this or how to diagnose the problem? Any suggestions on how I might modify the macro to prevent the error (I've already tried keeping automatic recalculate off and calculating in various stages of the macro - no difference).
Thanks!
Sub CopyInsert()
' Macro to copy and insert a line or range
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub
Here's the error:
"Run-Time error '-2147417848 (80010108)':
Automation Error
The object invoked has disconnected from its clients." I've tried Debug and it doesn't help - only solution is to kill excel and restart.
I use this macro in a dozen or more spreadsheets on a regular basis and on most it works fine but on some it causes excel to crash (the files are in two types with slight formula differences as to whether it is looking at gas or electricity prices stored in a single external spreadsheet - it's always the gas ones that fail for no obvious reason). In the files where it crashes it seems to cause it only on certain sheets and always on the same ones - so it will work fine on one sheet then moving to another which performs calculations on data in the first and it crashes. When I run the macros in the same file on a colleagues machine (a desktop) it's fine.... So I thought this might be a hardware / memory issue - I've just changed laptops and upgraded the newer laptop from 4GB RAM to 8GB RAM but no improvement. While writing this I've found that in a file it previously errored it didn't when I just tried it so it is clearly an intermittent problem and may be related to what else happens to be running on the machine at the time. The spreadsheet I'm running the macro on isn't small (around 10MB) as is the file containing the raw data. It doesn't seem to matter whether I have the raw data file open or not except that the links are updated quicker when it's open obviously.
Any suggestions on what might be causing this or how to diagnose the problem? Any suggestions on how I might modify the macro to prevent the error (I've already tried keeping automatic recalculate off and calculating in various stages of the macro - no difference).
Thanks!
