Method Range of object _Global failed

[scottleger4]

Good morning. I'm at a bit of a loss as to what's happening, hopefully you guys can help.

I have a large data query that I run every day, usually takes about an hour or so to run. I'm trying to setup an "auto run" using task manager. Task manager will open a different file (SC auto run) with a private sub to run my macro upon opening the workbook.

Private Sub Workbook_Open()

    OpenMEGA
    UpdateMega
    
End Sub

Those individual macros are as follows, stored in the main file (not the auto run file).

Sub OpenMEGA()

    Workbooks.Open Filename:= _
        "O:\OPS\Wdrive\public\#RM Reports\YMTD data dump\MEGA Files\YMTD weekly & month w trends chnl dwe car w book week SC.xlsm" _
        , UpdateLinks:=0
End Sub

Sub UpdateMega()

    Windows("YMTD weekly & month w trends chnl dwe car w book week SC.xlsm").Activate
    Application.Run "'YMTD weekly & month w trends chnl dwe car w book week SC.xlsm'!Macro1"
    
End Sub

The auto run file opens on time properly (task scheduler works), the first macro (OpenMEGA) executes properly, but then after some time I get the run time error 1004 in the subject line. I hit debug and it brings me to the second line in my "UpdateMega" macro. Can't figure out why, the syntax works in other workbooks, the names match, and I know the macro itself is good as I can successfully run ad-hoc. Just at the end of my understanding/troubleshooting. Thanks in advance.

 

[RoryA]

What's the code for Macro1? It sounds like the error may be there.

 

[scottleger4]

here it is...but that shouldn't be the problem. If there was something wrong with the macro itself, it should fail if I run it individually. Run as an ad-hoc from the main file works just fine.

Sub Macro1()

'this is a comment

    'Windows("YMTD weekly & month w trends chnl dwe car w book week SC.xlsm").Activate
    
    Application.DisplayAlerts = False
    'Applicaton.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Sheets("Results").Visible = True
    Sheets("Results").Select
    Range("Table_Query_from_YM[[#Headers],[ARE]]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    
    Sheets("RM").Visible = True
    Sheets("RM").Select
    Range("BML_DATA_Live_Link_Qry_for_AP_YMTD_MEGA[[#Headers],[AREA]]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Application.Calculation = xlCalculationAutomatic
    
    ModifyGroupTop100Data
    
    'ActiveWorkbook.RefreshAll
    
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
        If pc.SourceType = xlDatabase Then pc.Refresh
    Next pc
    
    Range("J5").Value = DateTime.Now
    
    Dim slcr As SlicerCache
    For Each slcr In ActiveWorkbook.SlicerCaches
        slcr.ClearManualFilter
    Next slcr

    Sheets("RM").Visible = False
    Sheets("Results").Visible = False
    
    ActiveWorkbook.Save
    Email
    
    Application.DisplayAlerts = True
    'Applicaton.ScreenUpdating = True
    
    'Application.OnTime TimeValue("06:15:00"), "Macro1"
    
End Sub

 

[RoryA]

The point is that there is no mention of Range in the code you posted initially, so the issue has to be in the code being called by that code. I suggest you properly qualify your range objects and skip the selecting - eg. use:

Sheets("Results").Range("Table_Query_from_YM[[#Headers],[ARE]]").ListObject.QueryTable.Refresh BackgroundQuery:=False

rather than:

Sheets("Results").Select
    Range("Table_Query_from_YM[[#Headers],[ARE]]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Or better still, refer to the listobject directly since that's what you want:

Sheets("Results").Listobjects("Table_Query_from_YM").QueryTable.Refresh BackgroundQuery:=False

 

[scottleger4]

So, if I read this correctly, your solution would be to re-write the main Macro1 to be...

Sub Macro1()

'this is a comment

    'Windows("YMTD weekly & month w trends chnl dwe car w book week SC.xlsm").Activate
    
    Application.DisplayAlerts = False
    'Applicaton.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Sheets("Results").Visible = True
    
    Sheets("Results").ListObjects("Table_Query_from_YM").QueryTable.Refresh BackgroundQuery:=False
    
    
    Sheets("RM").Visible = True
    Sheets("RM").Select
    Range("BML_DATA_Live_Link_Qry_for_AP_YMTD_MEGA[[#Headers],[AREA]]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Application.Calculation = xlCalculationAutomatic
    
    ModifyGroupTop100Data
    
    'ActiveWorkbook.RefreshAll
    
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
        If pc.SourceType = xlDatabase Then pc.Refresh
    Next pc
    
    Range("J5").Value = DateTime.Now
    
    Dim slcr As SlicerCache
    For Each slcr In ActiveWorkbook.SlicerCaches
        slcr.ClearManualFilter
    Next slcr

    Sheets("RM").Visible = False
    Sheets("Results").Visible = False
    
    ActiveWorkbook.Save
    Email
    
    Application.DisplayAlerts = True
    'Applicaton.ScreenUpdating = True
    
    'Application.OnTime TimeValue("06:15:00"), "Macro1"
    
End Sub

I can give this a sho and see what happens. Stay tuned.

 

[RoryA]

You missed the other one. Use:

Sheets("RM").Listobjects("BML_DATA_Live_Link_Qry_for_AP_YMTD_MEGA").QueryTable.Refresh BackgroundQuery:=False

rather than:

Sheets("RM").Select
Range("BML_DATA_Live_Link_Qry_for_AP_YMTD_MEGA[[#Headers],[AREA]]").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

 

[scottleger4]

I replaced both lines you recommended and am still having the same issue. I'm going to attempt to run JUST Marco1 (the original code before we made changes) in the regular file and cut out the AutoRun file altogether as a test to make sure nothing went wonky. If that code executes properly, then the problem shouldn't be with the coding of Macro1. Any other ideas?