PDA

View Full Version : [SOLVED] VBA - automation error the object invoked has disconnected



raykayplay
02-10-2015, 10:16 PM
Hello All,

I have the below sub which basically does the following:
1- Filter range in the active sheet.("Working template")
2- It copies visible cells.
3- Unhides and activates another sheet. ("TML1")
4- Pastes the copied data in the ("TML1") sheet.

Initially the sub worked fine. However after running other macros and putting further data in, the run time error "automation error the object invoked has disconnected" pops up and crashes excel.
If someone could you shed some light on what is going wrong with this sub as i have read through and through in other related thread but non relate to this condition.

I have "Debugged" once and it pointed out to the following: ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2

I deleted that line however the problem continued. Any help would be greatly appreciated.

Thank you in advance



Sub TMLone()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.AutoFilterMode = False
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2
Range("F11:N2840").Select
Selection.AutoFilter
On Error GoTo ErrorHandlerTML1
ActiveSheet.Range("$F$11:$N$2840").AutoFilter Field:=1, Criteria1:="<>"
ActiveSheet.Range("$F$11:$N$2840").AutoFilter Field:=4, Criteria1:="=M*", _
Operator:=xlOr, Criteria2:="=M*"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("TML1").Visible = True
Sheets("TML1").Select
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Working Template").Select
Selection.AutoFilter
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Range("F13").Select
Sheets("TML1").Select
Range("A1").Select
Application.EnableEvents = True
Exit Sub
ErrorHandlerTML1:
MsgBox "There are No M jobs"
Err.Clear
Exit Sub

Jan Karel Pieterse
02-11-2015, 09:49 AM
It may sometimes help to add an Application.Calculate line just before the line(s) of code causing the crash.

raykayplay
02-11-2015, 04:01 PM
It may sometimes help to add an Application.Calculate line just before the line(s) of code causing the crash.

Thank you Jan. Unfortunately this does not resolve the issue.

Jan Karel Pieterse
02-11-2015, 10:22 PM
How are these 'further macros' called precisely and what do they do?

raykayplay
02-12-2015, 12:19 AM
How are these 'further macros' called precisely and what do they do?

Thank you Jan.

There is no intersection between the other macros and this one. They do work in the same fashion though. Meaning they all involve copy/paste or insert.

I did manage to resolve the issue as i managed to locate the error line in one of the episodes and it was the shows levels line.

I had remove the data grouped and have remove those line and it worked fine.

Somehow using data grouping was crippling excel.

In any case thank you for your interest and appreciate your input.

Jan Karel Pieterse
02-12-2015, 02:15 AM
OK, thanks for letting us know.