How to Create Index Sheet Hyper-linked to Other Sheets in Excel

The problem is creating a detailed statistical analysis report with multiple sheets in a single Excel file. The target audience is middle-level management that doesn't require summaries but detailed analyses. And the report should be self-explanatory and easy to switch through the required sheets.

First of all, name all the sheets so that they speak for the nature of the data contained within.

Now the solution is to insert another sheet at the start and create links from that sheet to all other sheets in the file.

To solve the above problem, Create an ‘Index' sheet. But it is not easy to add hyperlinks to 18 other sheets manually. It would take lots of time and energy.

I wrote a small code snippet to create an index file using VBA.

Simply open the VBA editor (ALT+F11) and paste the below function there. Run the macro ‘createIndex' from the Macro list. It will create a new sheet named ‘Index' at the start of the document. Names of all the sheets are copied to the newly created sheet and those names are linked to the sheet as well. Please make sure that when you run this macro, there is no sheet named Index already.

Sub createIndex()
Dim i As Integer
    Sheets.Add before:=Sheets(1)
    Sheets(1).Name = "Index"
    counter = Sheets.Count
    i = 2
    Do
        Range("A" & i).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
            Address:="", _
            SubAddress:="'" & _
            Sheets(i).Name & "'!A1", _
            TextToDisplay:=Sheets(i).Name
        i = i + 1
    Loop Until i = counter + 1
End Sub