Create a Sheet Index in Excel

We all deal with multiple sheets in a single workbook, don’t we? Here is a smart way to create an Index of all your Sheets. You can click on the sheet name to navigate to that sheet.

Here is how we do it

Assume that we have 5 Sheets

And we would like to have an Index placed (in a new sheet) with the sheet names hyperlinked to the respective sheet.

It can be done with a simple VBA Code

Here comes a Code

Sub CreateIndex()
Dim sheetnum As Integer
Sheets.Add before:=Sheets(1)
For sheetnum = 2 To Worksheets.Count
 ActiveSheet.Hyperlinks.Add _
 Anchor:=Cells(sheetnum - 1, 1), _
 Address:='', _
 SubAddress:=''' & Worksheets(sheetnum).Name & ''!A1', _
 TextToDisplay:=Worksheets(sheetnum).Name
Next sheetnum
ActiveWindow.DisplayGridlines = False
End Sub

Follow the steps

1. Copy this Code
2. Open the excel workbook where you want to create a Sheet Index
3. Press the shortcut Alt + F11 to open the Visual Basic Window
4. In the Insert Menu, click on Module or use the shortcut Alt i m to add a Module. Module is the place where the code is written
5. In the blank module paste the code and close the Visual Basic Editor
6. Then use the shortcut Alt + F8 to open the Macro Box. You would have the list of all the macros here
7. You would see the Macro that you have just pasted in the Module as ‘CreateIndex’
8. Run it. You would see an Index with all the sheet names hyperlinked to the respective sheet.

Other Useful Macros

1. Automated Filter with Macro
2. Unhiding Multiple Sheets at Once
3. Covert Numbers into Indian Currency Words