Excel: How to create a Search Bar to Allow me to search and send me to certain tabs

Question

I have a master Excel sheet with over 150 Tabs. I am trying to create a search bar that will allow me to easily view these named tabs in alphabetical order and take me to those tabs once clicked.

Answer 1

I would use the following VBA code to create a list of the tab names as hyperlinks in a new sheet called 'Index', then manually sort it.

Sub generate_DOWN_list_with_hyperlinks_of_sheets_in_the_Active_Workbook()
Dim ws As Worksheet

If MsgBox("Do you want to create a list, starting in the active cell, of all sheets in this work book?", vbYesNo) = vbNo Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets
        'insert name
        ActiveCell.Value = ws.Name
        'insert hyperlink
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", _
            TextToDisplay:=ws.Name
        ActiveCell.Offset(1, 0).Activate
Next ws     
End Sub