How to Generate an Index of Excel Sheets Dynamically

This post is about "How to Generate an Index of Excel Sheets Dynamically" which is very helpful when you are dealing with Large excel files with lots of sheets/tabs.

Step 1. Create a New Sheet called "Index"
Step 2. Right click on the sheet and go to View Code ( It will open VB Editor )
Step 3. Copy the following VB Script there

 Private Sub Worksheet_Activate()
 Dim wSheet As Worksheet
 Dim l As Long
 l = 1
   With Me
     .Columns(1).ClearContents
     .Cells(1, 1) = "INDEX"
     .Cells(1, 1).Name = "Index"
   End With
   For Each wSheet In Worksheets
     If wSheet.Name <> Me.Name Then
       l = l + 1
         With wSheet
           .Range("A1").Name = "Start_" & wSheet.Index
           .Hyperlinks.Add Anchor:=.Range("C1"), Address:="", _
           SubAddress:="Index", TextToDisplay:="Back to Index"
         End With
         Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
         SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
     End If
   Next wSheet
 End Sub

Step 4. Now save and close the VB Editor
Step 5. Go to a different sheet and come back to Index Sheet
Step 6. Now you can see that the Index has been generated along with the list of sheets with links
Step 7. when you go to a sheet you can see another link to go back to index sheet as well.