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
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