• Index: Create List of Worksheet Names with Hyperlinks

    Ever came across this question !!!

    What is the maximum limit for number of sheets in Excel?

    Answer is: There is no limit explicitly defined. It varies depending on system memory. As many worksheets we create, it becomes difficult to traverse through them.

    How do we refer them?

    Easy method is to use [Control + PageDown or PageUp] arrows. Other ways are:

    Different Methods to refer Worksheets

    1. By creating Hyperlinked Index with List of Worksheets
    2. Show Worksheet List Option

    Now, lets proceed to create a Index with all worksheet names plus hyperlinks.

    Also Read: Check If Outlook is Running - then Launch Outlook app - vba code

    1. Excel Macro to Create Worksheet Index with URLs

    Vba code here performs these steps to create a Summary or Index for the Worksheets in Excel.

    • Create a new worksheet with name “Summary” or “Index”.
    • Loop through all the worksheets & get the name of each sheet.
    • Enter all worksheet names in the new Sheet.
    • Insert a Hyper Link to all the sheets in the Index Sheet for easy access.
    • Once you click on this link, the corresponding sheet should open up.

    Now, we will be ready with a Index sheet displaying the name of each sheet with a Hyperlink. Lets see how to make this with VBA macro code.


    Note: Above code will generate a entry for Index sheet itself. Customize the above code if you do not want an entry to Index.

    Every time, the above code is executed, the Index will get refreshed and if you have manually edited something in index sheet, those data will be erased.

    So be cautious before hitting the run or execute button to generate the Index.

    2. Shortcut to Show List of Worksheets

    Assume, some user has created more than 100 sheets in a excel workbook. How are we going to choose the desired sheet?

    1. Right click on the ‘left hand bottom scroll arrows’ (as in this image) in excel workbook.
    2. It will show all the sheets in a pop up window
    3. You can choose the sheet that you want to browse.

     

    Create_Sheet_Index_Worksheet_Names_Hyperlinks_Excel

    Create_Sheet_Index_Worksheet_Names_Hyperlinks_Excel

    This Index sheet will be more useful, if the tab or sheets are with some meaningful names rather than ‘Sheet1’, ‘Sheet2’ and so on. No one will understand the meaning if it has the default names.

    Also Read: Different Methods to Refer Excel Sheet or Cell

    Vba code to display – Worksheet List

    This is a small piece of code that can show the list of sheets. It can be assigned to a keyboard shortcut as explained in this topic.

    More Tips: How to get Folders List and Size

    Though there is no defined limit for number of worksheets, Excel does have limitations for number of Rows, Columns & few other things as mentioned here.


    Previous Post:
    Next Post:


    • Wolfgang

      Did not work Correctly with my Workbook Replaced this Line : ThisWorkbook.Sheets(“Index”).Hyperlinks.Add Anchor:=ThisWorkbook.Sheets(“Index”).Cells(Sheet_Idx, 1), Address:=””, SubAddress:= _
      ThisWorkbook.Sheets(Sheet_Idx).Name & “!A1”, TextToDisplay:=ThisWorkbook.Sheets(Sheet_Idx).Name

      with this

      ThisWorkbook.Sheets(“Index”).Hyperlinks.Add Anchor:=ThisWorkbook.Sheets(“Index”).Cells(Sheet_Idx, 1), Address:=””, SubAddress:= _
      “‘” & ThisWorkbook.Sheets(Sheet_Idx).Name & “‘” & “!A1”, TextToDisplay:=ThisWorkbook.Sheets(Sheet_Idx).Name