run-time error '2147417848 (80010108)': Automation error

[YounesB3]

Hello, I'm having this error when running a code: run-time error '2147417848 (80010108)': Automation error

I've highlighted in red where I get the error below:

Sub Z_YoY_Staffing()


Application.DisplayAlerts = False
Dim Sht As String
Dim x As Integer
Dim rangeName As Name


    Workbooks.Open "C:\ZTemp\Master File\Year Over Year - Staffing.xlsx"


    Windows("Year Over Year - Staffing.xlsx").Activate


    Sheets("TW").Select
    ActiveWindow.SelectedSheets.Delete
    Windows("Master File.xlsm").Activate
    Sheets("TW").Select
    Application.Run "'Master File.xlsm'!TW_PERM_CurrentW"
[COLOR=#ff0000]    Sheets("TW").Copy Before:=Workbooks("Year Over Year - Staffing.xlsx").Sheets(1)[/COLOR]
    Windows("Year Over Year - Staffing.xlsx").Activate
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 6", "Button 2", "Button 3", "Button 4", "Button 5")).Select
    Selection.Delete
    
    Range("B3").Select
    Cells.Find(What:="STAFFING", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 6).Select
    x = ActiveCell.Row
    Rows("4:" & x).Select
    Selection.Delete Shift:=xlUp
    
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sht = "TW"
    On Error Resume Next
        For Each rangeName In Application.Names
             rangeName.Delete
        Next rangeName
    On Error GoTo 0
    Range("D3").Select
    
    
    Sheets("PERM").Select
    ActiveWindow.SelectedSheets.Delete
    Windows("Master File.xlsm").Activate
    Sheets("PERM").Select
    Application.Run "'Master File.xlsm'!TW_PERM_CurrentW"
    Sheets("PERM").Copy After:=Workbooks("Year Over Year - Staffing.xlsx").Sheets(1)
    Windows("Year Over Year - Staffing.xlsx").Activate
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 6", "Button 2", "Button 3", "Button 4", "Button 5")).Select
    Selection.Delete
    
    Range("B3").Select
    Cells.Find(What:="STAFFING", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 6).Select
    x = ActiveCell.Row
    Rows("4:" & x).Select
    Selection.Delete Shift:=xlUp
    
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sht = "PERM"
    On Error Resume Next
        For Each rangeName In Application.Names
             rangeName.Delete
        Next rangeName
    On Error GoTo 0
    Range("D3").Select


    Workbooks("Year Over Year - Staffing.xlsx").Close SaveChanges:=True


Application.DisplayAlerts = True


End Sub

After some research, I've found a lot of people having the same error code with this at the end of the error: "The object invoked has disconnected from its clients."

I do not have that. I've tried some of the reg edit solutions and it doesn't work.

So what I did is check if my "Year Over Year - Staffing.xlsx" was corrupted somehow. I recreated the file with the tabs "TW" and "PERM". Here's the results of my little experiment: the macro worked the first time. I ran it again to make sure, but the second time, the macro stopped at the exact same place and with the exact same error.

What's up with that?

Thanks for your help!

Cross-post: http://www.excelforum.com/excel-pro...48-80010108-automation-error.html#post3742815

 

[Andrew Poulsom]

What does 'Master File.xlsm'!TW_PERM_CurrentW do and why do you need to Run it rather than Calling it? And where have you put your VBA code?

 

[YounesB3]

What does 'Master File.xlsm'!TW_PERM_CurrentW do and why do you need to Run it rather than Calling it? And where have you put your VBA code?

Yeah, I actually migrated the code from another Workbook, but I forgot to change that. Thanks! Fixed.


The macro being called is just hiding columns and unhiding based on some cell values.

[/I][/FONT]Sub TW_PERM_CurrentW()

Application.ScreenUpdating = False
Columns("J:BO").Select
Selection.EntireColumn.Hidden = False


For i = 10 To 61
    If Cells(1, i) = False Then
    Columns(i).Hidden = True
    End If
Next i


Application.ScreenUpdating = True
Range("D3").Select


End Sub[FONT=arial][I]

My Code is in Module 8. Does that change something?

I deleted module 1 & 2 because the code in here was no longer necessary.

I have code in module 3-4-6-7-8 (with multiple subs). Module 5 is blank for some reason.

Is it because I deleted the first 2 modules? I'm not used to play with that...

 

[Andrew Poulsom]

It should be OK in a General module. Are you still getting the error?

 

[YounesB3]

It should be OK in a General module. Are you still getting the error?

Yep :/

 

[Andrew Poulsom]

How many rows are there on the sheets in workbook "Year Over Year - Staffing.xlsx"?

 

[YounesB3]

How many rows are there on the sheets in workbook "Year Over Year - Staffing.xlsx"?

It depends, but around 300 only for each tab (TW & PERM).

 

[Andrew Poulsom]

No, total available rows.

 

[YounesB3]

No, total available rows.

1048576

 

[Andrew Poulsom]

Not sure then. What if you qualify the worksheet to be copied?

Workbooks("Master File.xlsm").Sheets("TW").Copy Before:=Workbooks("Year Over Year - Staffing.xlsx").Sheets(1)

assuming I got the right one.