VBA code for opening 2 files in different file paths

[blueeagles003]

Hi All,

I am new to VBA and I am trying to have one sub open 2 files that are stored in different file paths. The below is my current code:

Sub ExceptionReportSCV()
'
' ExceptionReportSCV Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Workbooks.Open Filename:=Sheets("Execute").Range("F1")
Workbooks.Open Filename:=Sheets("Execute").Range("F2")


And the below is what is being referenced in sheet "Execute" within cells F1 & F2:

cell F1 is: S:\Institutional Trading\Automated Reports\Model Exception Reports\Small Cap Value\SCV_Model_Exc_Report_02272019.xlsx

cell F2 is: S:\Institutional Trading\Daily Sign Offs\Exception Reports\SCV\SCV_Model_Exc_Report_02262019.xlsx

When running my macro I get a Run-time error '9': Subscript out of range. The first workbook referencing cell F1 opens successfully but the second command calling cell F2 fails to open. Curiously, when I try to have the macro just call the file path in F1 or F2 they both work successfully, but when I have the two commands under the same Sub, the second command fails. Does anyone have any insight to how I can rework this code to have both files open through one sub? Thank you!

 

[DanteAmor]

The problem is because when you open the first book, in that book the "exeute" sheet does not exist, that's why it sends you the error.

Try:

    Set sh = Sheets("Execute")
    Workbooks.Open Filename:=sh.Range("F1")
    Workbooks.Open Filename:=sh.Range("F2")

Or :

    Workbooks.Open Filename:=ThisWorkbook.Sheets("Execute").Range("F1")
    Workbooks.Open Filename:=ThisWorkbook.Sheets("Execute").Range("F2")