CODEWITHSUNDEEP
vbaexcel
Afiq Rosli
Afiq Rosli

Reputation: 385

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

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.

Upvotes: 0

Views: 936

Answers (1)

GMalc
GMalc

Reputation: 2628

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"

Upvotes: 1

Related Questions