Automation error The Object invoked has disconnected from its clients.

C

cstuder

New Member
Joined
May 15, 2023
Messages
20
Office Version
  1. 2021
Platform
  1. Windows
I run two reports then combine them together. I then run consecutive macros to format. Today, I get this error message which is mind boggling to me as I have run these macros for a couple of years (and use 2-3 times per week. Our IT department, mentioned it could be a memory issue. They cleared out all my history, cache, and downloads. It seems to get caught on the copy/paste instruction. Any idea what caused this to happen and how to fix? I appreciate your help!!!

Picture1.png
Picture2.png


Sub phocasicscicswi()

'

' phocasicscicswi Macro

' combine icsc icsw phocas

'

' Keyboard Shortcut: Ctrl+b

'

Cells.Select

With Selection.Font

.Name = "Calibri"

.Size = 10

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.colorIndex = xlAutomatic

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

Range("D2").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],icsw!C[-3],1,FALSE)"

Selection.AutoFill Destination:=Range("D2:D200000")

Range("D2:D200000").Select

Columns("D:D").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("A1").Select

Sheets("icsw").Select

Range("D2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],icsc!C[-3],1,FALSE)"

Selection.AutoFill Destination:=Range("D2:D200000")

Range("D2:D200000").Select

ActiveWindow.ScrollRow = 3

ActiveWindow.ScrollRow = 4

ActiveWindow.ScrollRow = 6

ActiveWindow.ScrollRow = 7

ActiveWindow.ScrollRow = 8

ActiveWindow.ScrollRow = 11

ActiveWindow.ScrollRow = 13

ActiveWindow.ScrollRow = 14

ActiveWindow.ScrollRow = 16

ActiveWindow.ScrollRow = 23

ActiveWindow.ScrollRow = 26

ActiveWindow.ScrollRow = 28

ActiveWindow.ScrollRow = 31

ActiveWindow.ScrollRow = 34

ActiveWindow.ScrollRow = 36

ActiveWindow.ScrollRow = 39

ActiveWindow.ScrollRow = 43

ActiveWindow.ScrollRow = 46

ActiveWindow.ScrollRow = 49

ActiveWindow.ScrollRow = 51

ActiveWindow.ScrollRow = 55

ActiveWindow.ScrollRow = 59

ActiveWindow.ScrollRow = 64

ActiveWindow.ScrollRow = 67

ActiveWindow.ScrollRow = 109

ActiveWindow.ScrollRow = 113

ActiveWindow.ScrollRow = 116

ActiveWindow.ScrollRow = 123

ActiveWindow.ScrollRow = 126

ActiveWindow.ScrollRow = 129

ActiveWindow.ScrollRow = 133

ActiveWindow.ScrollRow = 135

ActiveWindow.ScrollRow = 138

ActiveWindow.ScrollRow = 140

ActiveWindow.ScrollRow = 143

ActiveWindow.ScrollRow = 166

ActiveWindow.ScrollRow = 169

ActiveWindow.ScrollRow = 172

ActiveWindow.ScrollRow = 175

ActiveWindow.ScrollRow = 178

ActiveWindow.ScrollRow = 180

ActiveWindow.ScrollRow = 183

ActiveWindow.ScrollRow = 185

ActiveWindow.ScrollRow = 188

ActiveWindow.ScrollRow = 190

ActiveWindow.ScrollRow = 192

ActiveWindow.ScrollRow = 194

ActiveWindow.ScrollRow = 197

ActiveWindow.ScrollRow = 198

ActiveWindow.ScrollRow = 201

ActiveWindow.ScrollRow = 217

ActiveWindow.ScrollRow = 219

ActiveWindow.ScrollRow = 223

ActiveWindow.ScrollRow = 226

ActiveWindow.ScrollRow = 228

ActiveWindow.ScrollRow = 229

ActiveWindow.ScrollRow = 231

ActiveWindow.ScrollRow = 232

ActiveWindow.ScrollRow = 234

ActiveWindow.ScrollRow = 236

ActiveWindow.ScrollRow = 238

ActiveWindow.ScrollRow = 240

ActiveWindow.ScrollRow = 241

ActiveWindow.ScrollRow = 243

ActiveWindow.ScrollRow = 245

ActiveWindow.ScrollRow = 246

ActiveWindow.ScrollRow = 244

ActiveWindow.ScrollRow = 236

ActiveWindow.ScrollRow = 229

ActiveWindow.ScrollRow = 206

ActiveWindow.ScrollRow = 198

ActiveWindow.ScrollRow = 181

ActiveWindow.ScrollRow = 174

ActiveWindow.ScrollRow = 117

ActiveWindow.ScrollRow = 110

ActiveWindow.ScrollRow = 91

ActiveWindow.ScrollRow = 84

ActiveWindow.ScrollRow = 28

ActiveWindow.ScrollRow = 23

ActiveWindow.ScrollRow = 12

ActiveWindow.ScrollRow = 6

ActiveWindow.ScrollRow = 2

Columns("D:D").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("icsc").Select

Columns("D:D").Select

Application.CutCopyMode = False

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("A1").Select

Application.CutCopyMode = False

Sheets("icsc").Select

Sheets("icsc").Name = "icsc icsw"

Range("A1").Select

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 1

End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
You have a lot of "junk" in your code that can be removed, such as all the ScrollColumn statements (obviously, recorded with Macro Recorder), and most of the Select statements (toy typically do not need to select ranges in order to work with them).

I think you can simplify your code down to this:
VBA Code: 
Sub phocasicscicswi()
' phocasicscicswi Macro
' combine icsc icsw phocas
'
' Keyboard Shortcut: Ctrl+b
'

With Cells.Font
    .Name = "Calibri"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With

Range("D2:D200000").FormulaR1C1 = "=VLOOKUP(RC[-3],icsw!C[-3],1,FALSE)"
Range("D2:D200000").Value = Range("D2:D200000").Value

Sheets("icsw").Select
Range("D2:D200000").FormulaR1C1 = "=VLOOKUP(RC[-3],icsc!C[-3],1,FALSE)"

Sheets("icsc").Select
Columns("D:D").Value = Columns("D:D").Value
Application.CutCopyMode = False

Sheets("icsc").Name = "icsc icsw"
Range("A1").Select

End Sub
Also, we may be able to make it more efficient still. Do you really need to go down to row 200000 one very sheet?
You may be able to dynamically find the last row with data, and only go down that far, if you let us know which column we can look at to find the last row (i.e. maybe column C?).
And what sheet are you on when you kick off the code?
 
Upvote 0
Joe4 said:
You have a lot of "junk" in your code that can be removed, such as all the ScrollColumn statements (obviously, recorded with Macro Recorder), and most of the Select statements (toy typically do not need to select ranges in order to work with them).

I think you can simplify your code down to this:
VBA Code: 
Sub phocasicscicswi()
' phocasicscicswi Macro
' combine icsc icsw phocas
'
' Keyboard Shortcut: Ctrl+b
'

With Cells.Font
    .Name = "Calibri"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With

Range("D2:D200000").FormulaR1C1 = "=VLOOKUP(RC[-3],icsw!C[-3],1,FALSE)"
Range("D2:D200000").Value = Range("D2:D200000").Value

Sheets("icsw").Select
Range("D2:D200000").FormulaR1C1 = "=VLOOKUP(RC[-3],icsc!C[-3],1,FALSE)"

Sheets("icsc").Select
Columns("D:D").Value = Columns("D:D").Value
Application.CutCopyMode = False

Sheets("icsc").Name = "icsc icsw"
Range("A1").Select

End Sub
Also, we may be able to make it more efficient still. Do you really need to go down to row 200000 one very sheet?
You may be able to dynamically find the last row with data, and only go down that far, if you let us know which column we can look at to find the last row (i.e. maybe column C?).
And what sheet are you on when you kick off the code?
Click to expand...

Why of course it's by recorder :), it's the only way I know! Self-teaching takes time that I don't have. :) I figured when I sent this in, you'd laugh at the length....
Click to expand...
I only need to go to the last blank row, which I have discovered the correct code but never took the time to fix. If you can add it, that would be awesome!!!! Anywhere I have 200000, the code needs run to the last blank row. I believe I am on the icsc sheet when I start it, but never thought about it.

Any idea why this won't run today? I just ran this on Tuesday. Is it truly the length and using up memory?
 
Upvote 0
Try this:
VBA Code: 
Sub phocasicscicswi()
' phocasicscicswi Macro
' combine icsc icsw phocas
'
' Keyboard Shortcut: Ctrl+b
'

Dim lr As Long

With Cells.Font
    .Name = "Calibri"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With

lr = Cells(Rows.Count, "C").End(xlUp).Row

Range("D2:D" & lr).FormulaR1C1 = "=VLOOKUP(RC[-3],icsw!C[-3],1,FALSE)"
Range("D2:D" & lr).Value = Range("D2:D" & lr).Value

Sheets("icsw").Select
lr = Cells(Rows.Count, "C").End(xlUp).Row

Range("D2:D" & lr).FormulaR1C1 = "=VLOOKUP(RC[-3],icsc!C[-3],1,FALSE)"
Range("D2:D" & lr).Value = Range("D2:D" & lr).Value

Sheets("icsc").Name = "icsc icsw"
Range("A1").Select

End Sub

No idea why you got that error. But see if these updates prevent that. Maybe removing all the scrolling and not populating extra formulas will use less memory.
 
Upvote 0
Solution
I did copy/paste your code and it worked, but I have four other macros I run after this one. They all say the following and each time I run the macro, I get an error. Why would this error happen?

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
 
Upvote 0
cstuder said:
I did copy/paste your code and it worked, but I have four other macros I run after this one. They all say the following and each time I run the macro, I get an error. Why would this error happen?

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
Click to expand...
Sorry, I didn't see your response. I will just move on. Thanks for your help. I may send you the other macros for you to fix - you are so fast!
 
Upvote 0
You are welcome.
Glad I was able to help.

Please note, when posting code in the future, please use the Code tags as directed here: How to Post Your VBA Code
The way you posted your code makes it very hard to work with.
If you post it with the Code Tags, it will look like the code I posted, where indenting is maintained and the text is not all spread out.
 
Upvote 0
Joe4 said:
You are welcome.
Glad I was able to help.

Please note, when posting code in the future, please use the Code tags as directed here: How to Post Your VBA Code
The way you posted your code makes it very hard to work with.
If you post it with the Code Tags, it will look like the code I posted, where indenting is maintained and the text is not all spread out.
Click to expand...
Will do, thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
344
RoryA
RoryA
P
  • Question Question
Help with Vba Code
Replies
3
Views
463
Coyotex3
C
VinceF
  • Solved
VBA to Copy paste value based on value in cell
Replies
4
Views
479
VinceF
VinceF
An Quala
  • Question Question
Should I name it Active.Sheet or sheet name?
Replies
8
Views
896
MARK858
MARK858
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
280
willow1985
willow1985

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top