Opening another workbook, specific sheet | Run-time error '9': Subscript out of range

0
votes

I have 2 excel files

  1. stage1
    1. Menu
    2. Data
  2. stage2
    1. Calculation
    2. Charts

I am trying to jump back and forth from these two files linking to certain sheet of the workbook its jumping to.

Option Explicit

Dim stage1 As Workbook
Dim stage2 As Workbook

Sub Open_stage1_Menu()

    Set stage1 = Workbooks.Open(ActiveWorkbook.Path & "\stage1.xlsm")
    stage1.Sheets("Menu").Activate

End Sub

Sub Open_stage1_Data()

    Set stage1 = Workbooks.Open(ActiveWorkbook.Path & "\stage1.xlsm")
    stage1.Sheets("Data").Activate

End Sub

Sub Open_stage2_Calculation()

    Set stage2 = Workbooks.Open(ActiveWorkbook.Path & "\stage2.xlsm")
    stage2.Sheets("Calculation").Activate

End Sub

Sub Open_stage2_Charts()

    Set stage2 = Workbooks.Open(ActiveWorkbook.Path & "\stage2.xlsm")
    stage2.Sheets("Charts").Activate

End Sub

All of the Macros are stored in the stage1 workbook. I've implemented buttons to navigate through sheets, but jumping to stage2 cause error (Subscript out of range) but not towards stage1.

1
vbaexcel
That error means that either the workbook or the sheets in that workbook do not exist. check spelling and ensure there are not spaces or other non printable characters in the sheet names. – Scott Craner
Try debugging this by using Sheets() with a number, 1 for the first sheet and 2 for the second sheet etc. – Joel Spolsky
@ScottCraner I've check and certain there is no spelling error, everything is as posted :( – Afiq Rosli
@JoelSpolsky I'm really new in VBA programming, and I don't even know how to debug using Sheets(). Is it the one that "step in" (F8)? – Afiq Rosli
@AfiqRosli: Debugging works with F8 (Step in). With that procedure you execute every single line "by hand" via clicking F8. Using index numbers for the sheets (mentioned by @JoelSpolsky) instead of "Calculation" or "Charts". The error "Subscript out range" usually derives from the fact, that either the called workbook or the called sheet could not be found. You can also identify this kind of error using F8. – prextor

1 Answers

1
votes

Remove all your workbooks.open lines of code You only need to open the "stage2" workbook once. The "stage1" workbook is already open. You can use a button to open the "stage2" workbook or in a regular module.

Create these macros in your stage1 workbook

Sub Open_stage1_Menu()
    Workbooks("stage1.xlsm").Sheets("Menu").Activate
End Sub

Sub Open_stage1_Data()
    Workbooks("stage1.xlsm").Sheets("Data").Activate
End Sub

Sub Open_stage2_Calculation()
    Workbooks("stage2.xlsm").Sheets("Calculation").Activate
End Sub

Sub Open_stage2_Charts()
    Workbooks("stage2.xlsm").Sheets("Charts").Activate
End Sub

Add a new button to your stage1 workbook, sheet "Menu" and select "Open_stage2_Calculation"

Add another button to your stage1 workbook, sheet "Menu" and select "Open_stage2_Charts"

You can also add these 2 buttons to the other worksheet "Data"

Go to your stage2 workbook;

Add a new button to your stage2 workbook, sheet "Calculation"; in the "Assign Macro use the "Macros in: drop-down to select "stage1" workbook and select "Open_stage1_Menu"

Add another button to your stage2 workbook, sheet "Calculation" ; in the "Assign Macro use the "Macros in: drop-down to select "stage1" workbook and select "Open_stage1_Data"

You can also add these 2 buttons to the other stage2 worksheet "Charts"