Identify and go to Excel tab

At the bottom left of the Excel screen there is the tab navigation arrows. If you right click on these you will get a list of all sheets. With 100 tabs there will still be some scrolling but might be easier than scrolling through the tabs.

Alternatively, you could set up an index sheet with a list of a sheet names and a hyperlink to take you to each sheet.

Thanks, Rob, I knew about the first option but was hoping for more.  The hyperlink could work but it would require a lot of initial setup and ongoing maintenance which I was hoping to avoid.  But I will think on that one.

byundt -- Good points. I first was hoping there might be a built-in Excel function to help do this, but I guess I was just hoping.  The idea way to select would be to enter the six-digit number (ex. 234567) and it would go to that tab.  I think that is what you've lined out in the first macro, right?

Actually, the second macro does exactly what you are now saying. Run the macro, it asks which sheet you want, enter the six-digit number, and the macro takes you there.

The first macro tries to be even simpler. If you already had the six-digit number in a cell, just doubleclick that cell and an event sub takes you to that worksheet.

Setup of the hyperlinks on an index sheet wouldn't be that difficult. You can use the HYPERLINK function to create the links.

With Robs idea, you could have a macro to update the hyperlinks sheet.
Make a sheet with the name Index and run this macro.

Option Explicit

Sub MakeSheetIndex()
    Dim ws As Worksheet, wsIndex As Worksheet
    Dim rw As Integer
    
    Set wsIndex = ThisWorkbook.Worksheets("Index")
    wsIndex.Cells.Clear
    wsIndex.Range("A1") = "Sheet name"
    rw = 1
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsIndex.Name Then
            rw = rw + 1
            wsIndex.Cells(rw, 1).Formula = "=HYPERLINK(" & "'" & ws.Name & "'!A1,""" & ws.Name & """)"
        End If
    Next ws
    wsIndex.Range("A1").CurrentRegion.Sort Key1:="Sheet name", Order1:=xlAscending, Header:=xlYes
    wsIndex.Columns.AutoFit
End Sub

Select allOpen in new window

Another approach would be to use a UserForm equipped with a listbox, as in the attached sample.

The Userform reads all the worksheet names when activated. Double-clicking in the list takes you to the selected sheet.

A macro to open the Userform, showPicker, is in the code module mSheets. You could attach this to a button in the Quick Access toolbar, for instance.

Here is the code in the userform, which is very simple.

Option Explicit


Private Sub lbSheets_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'activate chosen sheet
Worksheets(lbSheets.Value).Activate
Me.Hide
End Sub

Private Sub UserForm_Activate()
Dim ws As Worksheet

'clear the list box
lbSheets.Clear
'reload the listbox
For Each ws In Worksheets
lbSheets.AddItem ws.Name
Next

End Sub

Select allOpen in new window

The upside of this approach is you do not need to go back to an "index sheet" each time you want to move sheets.
multisheet.xlsm

Wow..  Lot of options here.  I am looking at closely now.  Sorry for the slow response.  It has been a busy time for me working with the data.

Thanks all.  A lot of options...  Liked the VBA and non-VBA identified.

A non-VBA trick that I was not aware of until yesterday may be relevant. It's hard to beat no formulas and no code for a solution to a problem.

Would that work with INDIRECT? I suspect not.

So you'd have to manually/programmatically setup:
=Sheet1!A1
=Sheet2!A1
=Sheet3!A1
etc etc through to
Sheet100!A1

Rob,
My original question was how do you know which worksheet you want to view? tomfarrar hasn't said so far, but the answer will make it clear which is the best way to implement the required linkages.

Since the worksheets are named with an arbitrary six-digit number, you sure won't be pulling that info out the top of your head. That's why I thought there might already be data on the worksheet the user is looking at that provide the name of the target worksheet. If the data are formulas linking to certain cells on each worksheet, then the best solution to the problem will leverage that fact.

Brad

Hi All - Thought I had closed this out with my previous comment, but obviously I didn't.  

"Thanks all.  A lot of options...  Liked the VBA and non-VBA identified."
be
I will award now.  As to  byundt's comment:  The selection process could have worked  either way after thinking it through.  I could have built a list of the tab numbers on a separate sheet, but that would be a maintenance issue as more tabs are added (and there are many).  The better solution (as I perhaps poorly stated) was to enter the number and have the "procedure" take me to that page.  Anyway,,,,

Thanks again; sorry for the delay.

tomfarrar,
While I appreciate that you have found good solutions to your problem, I still feel that you haven't answered my question about how does the user know which worksheet he wants to go to? I am trying to focus on the user's needs. Entering the number and having a "procedure" take you to that page is OK, but doesn't seem like the friendliest way of addressing the user's needs.

Somehow, the user must decide which worksheet they want to go to. My question is how do they reach that decision? Does the user see that other worksheet name displayed somewhere? Does the user see data taken from that other worksheet (such as by a formula) and want to go there to investigate? Has the user memorized all the six digit codes and just knows which one they need next? Does the user play "spin the bottle" and choose a destination randomly?

Brad

@Brad - that's the upside of the UserForm approach, I think. You can add more data to the menu, and go anywhere from any sheet, without having to jump back to an index sheet.