VBA Code – Create Index of Worksheets – with Links to each Sheet

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.

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.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'          Visit our website for more Tips and Tricks                      '
'                ---- Officetricks.com ----                                '
'                                                                          '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Create_Index_Sheet()
    
    'Check whether a Index Sheet is already Present
    For Sheet_Idx = 1 To Sheets.Count
        If ThisWorkbook.Sheets(Sheet_Idx).Name = "Index" Then
            GoTo Create_Index:
        End If
    Next Sheet_Idx
    
    'Create a new sheet with name "Index"
    Sheets.Add
    ActiveSheet.Name = "Index"

'Loop Through Each Sheet and get its name
Create_Index:
    For Sheet_Idx = 1 To Sheets.Count
        ThisWorkbook.Sheets("Index").Cells(Sheet_Idx, 1) = ThisWorkbook.Sheets(Sheet_Idx).Name
        'Add hyperlink for easy access to other sheets from Index Sheet
        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
    Next Sheet_Idx
    
    'Process Completed
    MsgBox "Index Created"
    
End Sub

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.

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.

Sub Show_Sheet_Index_List()
    'Display list of sheets
    Application.CommandBars("Workbook tabs").ShowPopup
End Sub

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.