Create Automatic Index sheet in Excel

The purpose of this macro is to create an index sheet for your excel file. The index sheet will contain a list of all the worksheets available in the excel file.

Steps involved:

1. Open the excel file where you want to add index sheet
2. Insert a new sheet at the beginning of the file and rename it as “Index”
3. Press Alt+F11
4. A new visual basic window will open, in the left pane (project explorer), select the Index sheet and double click
5. In the right pane, where you can enter the macro commands, there will be 2 drop down boxes.
6. Select “Worksheet” in the first drop down box and select “Activate” in the second drop down box. Thus you are telling the system to run the macro whenever this worksheet is activated.

7. Paste the below code as it is:

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet

Dim M As Long

M = 1

With Me

.Columns(2).ClearContents

.Cells(1, 2) = "INDEX"

End With

For Each wSheet In Worksheets

If wSheet.Name <> Me.Name Then

M = M + 1

Me.Hyperlinks.Add Anchor:=Me.Cells(M, 2), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name

End If

Next wSheet

End Sub

8. Save the file and go to the Index sheet. You will notice that the index has been listed in column B.

9. This macro will run everytime you come to this sheet, which effectively means that index will be updated whenever you come to the sheet and you always see the latest list of sheets in that order.
10. You can click on any of the sheet names to go directly to that sheet.

Line by Line analysis of the macro:

1. Private Sub Worksheet_Activate() - even which triggers the macro.
2. Dim wSheet As Worksheet – define the variable which will be used later to identify the individual sheets
3. Dim M As Long – this will be used as a counter and to update the rows in the list one by one.
4. M = 1 – initiate the value of M at 1.
5. With Me – start working with the current worksheet where Me refers to the current worksheet, ie. Index sheet.
6. .Columns(2).ClearContents – for the current worksheet, delete the data in column B. This will delete any previously existing index
7. .Cells(1, 2) = "INDEX" – Enter the value in cell B2 as Index – heading of the list
8. End With – end working with the current worksheet.
9. For Each wSheet In Worksheets – “For” loop is used to start working with one sheet at a time and continue till all the sheets are done.
10. If wSheet.Name <> Me.Name Then – make sure that the system has not come back to the starting point, (i.e. index worksheet) and to avoid making a reference to index in the index sheet itself
11. M = M + 1 – increase M by 1. this will allow to update the next row in the index instead of overwriting the current row
12. Me.Hyperlinks.Add Anchor:=Me.Cells(M, 2), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name – create a hyperlink to the respective worksheet. As we can see, we are using the cell address (M,2) to update the list, this way, the system keeps moving one row down everytime it finds a new worksheet.
13. End If – end the if condition
14. Next wSheet – At this point, one index entry has been created. Next wsheet will ask the system to go to the next worksheet and follow steps i. to m. again.
15. End Sub – after all the sheets have been indexed. End the macro.

This macro is easily available on the internet if you along with a number of other variations. I have only taken one and simplified it for easy understanding.

Enjoy!!!