Unable to Open My Workbook at a Specific Tab

[DavidWF]

I'd appreciate guidance with the following.

I'm trying to open a workbook at a specific tab, using:

Private Sub Workbook_Open()

Sheets(“Sheet26”).Select

Range(“B2”).Select

End Sub

in ThisWorkbook, however every time I open the workbook it opens at the last used tab and displays an error pop-up - Run-time error '9': Subscript out of range

Running Debug highlights the

Sheets("Sheet26").Select

line. I've tried replacing "Sheet26" with the actual tab name but I get the same result.

What have I done wrong and what should I have done?

 

[mole999]

if you look at the vba, to the left, you see the name of the project, down that list will be the name and its sheet number, makes sure you are addressing the right sheet, adding and removing sheets can mean what worked now no longer references what you expected

 

[DavidWF]

The sheet listing at the left definitely shows Sheet26 being present. The tab name is Index so I've tried

Sheets(“Sheet26”).Select

and I've tried

Sheets(“Index”).Select

I get the error pop-up in both cases

 

[MARK858]

You are looking at its codename so try just

Sheet26.Select

For the Index part open your Immediate window if not already open, run the code below and copy the result to the thread.

Sub testit()
Debug.Print "< &"; Sheet26.Name; "& >", Len(Sheet26.Name)
End Sub

 

[DavidWF]

Thanks Mark - that now opens the workbook at the correct tab, but now there's a new error - the

Range(“B2”).Select

line.
Now it's Run-time error '1004' Method 'Range' of object '_Global' failed

 

[fhqwgads]

The sheet listing at the left definitely shows Sheet26 being present. The tab name is Index so I've tried

Sheets(“Sheet26”).Select

and I've tried

Sheets(“Index”).Select

I get the error pop-up in both cases

when referring to the sheet number instead of the name of the sheet i dont think you use quotation marks

Sheets(26).Select

i have no idea why using the name of the sheet "Index" isnt working for you though

 

[MARK858]

Thanks Mark - that now opens the workbook at the correct tab, but now there's a new error - the

Range(“B2”).Select

line.
Now it's Run-time error '1004' Method 'Range' of object '_Global' failed

Other than you have some strange quote marks around B2 (which I deleted and retyped) I don't get the issue.
Do you have any sheet protection or merged cells?

or try

Private Sub Workbook_Open()
Application.Goto Sheet26.Range("B2"), True
End Sub

 

[DavidWF]

Sorry Mark, I missed seeing the second part of your reply. The result from the Immediate Window was:

< &Index& >    5

 

[MARK858]

Sorry Mark, I missed seeing the second part of your reply. The result from the Immediate Window was:

< &Index& >    5

I would check the quote marks in your code on that as well then.

 

[DavidWF]

Thanks Mark

Application.Goto Sheet26.Range("B2"), True

solved the problem. The workbook now opens correctly.

No idea what the issue with the quote marks might be - maybe because we're upside down on this side of the planet.

Thanks guys for your comments and assistance.