Run-time error "2147417848 (80010108)': HELP!!!

[Mathman]

I Started to get this error lately when running a macro. I seem to get it when I run multiple instances of excel to run at the same time (10 at the same time).




MESSAGE BOX

Run-time error "2147417848 (80010108)':​

<strike></strike>

Automation error
The object invoked has disconnected from it's clients.




This is where the Error occurs Section
(shtStartNums.Rows("7:7").<wbr style="display: inline-block;">Select
Selection.PasteSpecial Paste:=<wbr style="display: inline-block;">xlPasteAllUsingSourceTheme,

Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False​

​

MACRO ​

​

'This function takes data in row 5 of the Start Numbers New sheet and pastes it into the main table.
'It effectively archives the data
Sub CopyStartNumbersDown(Optional IsSupercycle As Boolean = False)
Dim r As Range
CalcOff

Dim oldsh As Worksheet

Set oldsh = ActiveSheet
Set r = Selection


'make sure the sheet is selected
shtStartNums.Activate

'Check to see if we already archived for the day. if so, then give the choice to replace or cancel. Can only have one day
If (IsSupercycle = False And shtStartNums.Range("<wbr style="display: inline-block;">LiveDataStart").Value = shtStartNums.Range("<wbr style="display: inline-block;">LiveDataStart").Cells(3, 1).Value) Or (IsSupercycle = True And shtStartNums.Range("<wbr style="display: inline-block;">SuperCycleDateStart").Value = shtStartNums.Range("<wbr style="display: inline-block;">LiveDataStart").Cells(3, 1).Value) Then
' If MsgBox("Date already exists. Replace?", vbOKCancel) = vbCancel Then
' GoTo Cleanup
'
' End If
Else
'Insert if it doesn't exists
shtStartNums.Rows("7:7").<wbr style="display: inline-block;">Insert Shift:=xlDown, CopyOrigin:=<wbr style="display: inline-block;">xlFormatFromLeftOrAbove

End If

If IsSupercycle = False Then
shtStartNums.Rows("5:5").<wbr style="display: inline-block;">Select
Else
shtStartNums.Rows("3:3").<wbr style="display: inline-block;">Select
End If
Selection.Copy
shtStartNums.Rows("7:7").<wbr style="display: inline-block;">Select
Selection.PasteSpecial Paste:=<wbr style="display: inline-block;">xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


cleanup:
If Not r Is Nothing Then
oldsh.Activate
r.Select
End If

End Sub​

 

[Joe4]

I Started to get this error lately when running a macro. I seem to get it when I run multiple instances of excel to run at the same time (10 at the same time).

My first question would be, why are you running 10 instances at the same time?
Are you running multiple macros simultaneously?
If so, I can see how that might be an issue, since you are using code with ".Select" statements. That physically selects a cell in Excel.
So if you may multiple macros running at the same time, you can have the macros fighting over which cells are being selected.

I would advise you never to run multiple macros simultaneously.

 

[Mathman]

I'm running multiple instances to save time which cuts time down by 3/2
Yes they are all runing macros simultaniuously.

What you are saying is along the lineof what I was thinking. Would their be a workaround the issue. Perhaps if in the code I specify the sheets name since all instances have different names?

 

[Joe4]

Let's define what you mean by "instances".
By instances, do you mean different copies of the Excel file/macro saved with different names?
Or do, you mean opening different instances of Excel?

If you are opening different instances of Excel, then I think you may be OK, as if they are "truly" different instances, they cannot see one another. However, perhaps those select statements are moving the focus between different Excel instances.

However, there are a number of things you can do to speed up your code.

Firstly, get rid of all the ".Select" statements that you can. It is usually not necessary to select ranges to work with them.
So, most anytime you have one row of code that ends with ".Select", and the next begins with "Selection" or "ActiveCell", you can combine those two rows together, i.e.

Range("A1").Select
Selection.Copy

can be combined to:

Range("A1").Copy

Secondly, if you temporarily suppress calculations and screen updating while the code is running, it will process faster.
So place this block of code near the top of your macro:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

and then place this near the end of it:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True