VBA – Create a Clickable Index (Summary, Table of Contents) Sheet Macro

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

Sub GenIndexSheet()
    Dim Ws As Worksheet
    Dim i As Long: i = 1
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Create an Index Sheet. If already existing, clear it.
    On Error Resume Next
    Set Ws = Worksheets("Index")
    If Err.Number = 0 Then
        Worksheets("Index").ClearContents
        Else
        On Error GoTo 0
        Worksheets.Add(Before:=Worksheets(1)).Name = "Index"
    End If
    
    Worksheets("Index").Activate
    Range("A1") = "Index"
    Range("A1").Font.Bold = True
    Range("A1").Font.Size = 20
    
    For Each Ws In Worksheets
        If Ws.Name <> "Index" Then
            i = i + 1
            Ws.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
        End If
    Next Ws
    
    Worksheets("Index").Columns(1).AutoFit
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

A workbook containing the above macro can be downloaded from Generate Summary Sheet Macro