Problem running a Macro on Start-Uo

[yooperboy877]

I'm flummoxed by a situation I keep encountering, and am hoping that someone can shed light on this issue.

I have an Excel workbook with a series of Macros I want to run on startup. The first macro goes out to refresh data from an external SQL source. That refresh works perfectly when I configure the external connection to refresh on file opening. It also works when I click a button to which the relevant macro has been assigned. That macro is

Sub Data_Refresh()
'
' Data_Refresh Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Range("Table_iMan_BNALegal_vwWorkSpaceReport[[#Headers],[Project Name]]"). _
        Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

As originally set up, I manually triggered, using a button, a series of additional macros that sorted my data into other worksheets based on various criteria. That too works perfectly in manually mode. The button in question runs the following:

Sub Run_All_Macros()
    A_ClearAllOldContents
    B_ClearWorkingData
    C_CopyDataRangeToWorking
    D_CopyOpenContractMatters
    E_CopyNewContractMatters
    F_CopyClosedContractMatters
    CopyOpenDJWCases
    CopyOpenFMCases
    CopyOpenJLCases
    CopyOpenJHCases
    CopyOpenSECases
    CopyOpenKZCases
    CopyOpenRLCases
    CopyOpenUnassignedCases
    CopyOpenOthersCases
End Sub

What is causing problems is when I disable all, and then try to run all of the macros on file opening. I attempt to do that with a module located in This Workbook, coded as follows:

Private Sub Workbook_Open()
    Data_Refresh
    A_ClearAllOldContents
    B_ClearWorkingData
    C_CopyDataRangeToWorking
    D_CopyOpenContractMatters
    E_CopyNewContractMatters
    F_CopyClosedContractMatters
    CopyOpenDJWCases
    CopyOpenFMCases
    CopyOpenJLCases
    CopyOpenJHCases
    CopyOpenSECases
    CopyOpenKZCases
    CopyOpenRLCases
    CopyOpenUnassignedCases
    CopyOpenOthersCases
End Sub

Every time I run the workbook with the Workbook_Open macro, it crashes on the Data_Refresh portion, returning a 1004 error on the very first line. I get the same error when I open the Developer tab and try to run the Data_Refresh macro from there.

Why can I do this in the background and manually via button, but I can't do it automatically or through the Developer area?

Many thanks for the help on this.

 

[WarPigl3t]

Well the error 1004 is a range error. So the range is wrong. Let's brainstorm. Why would the range be wrong? Well it works when you push the button, so what worksheet is the button on? When you open the workbook, maybe that isn't the worksheet that displays and so the range is incorrect because its not the same worksheet you intended for the code to run. Try adding worksheet select code so it selects the worksheet with the button before it does the rest of the code.

 

[yooperboy877]

Try adding worksheet select code so it selects the worksheet with the button before it does the rest of the code.

Thanks for the quick replay, Mr. WarPig. Can you give me an example of "worksheet select code" - I'm a VBA greenhorn!

FYI, the workbook usually opens to Cover Sheet; the worksheet with the macro button is Master Data Sheet, and all of the macros in the Private Sub Workbook_Open(), including Data_Refresh(), are in modules, not assigned to specific sheets.

 

[mikerickson]

Try moving the code from Workbook_Open to Workbook_Activate.

 

[yooperboy877]

Try moving the code from Workbook_Open to Workbook_Activate.

Nope, that didn't do the trick. Still getting a 1004 error on the Data_Refresh macro.

 

[WarPigl3t]

Change the name in this code to the name of your master worksheet.

Sheets("Master").Select
Sheets("Master").Activate