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
- Copy this Code
- Open the excel workbook where you want to create a Sheet Index
- Press the shortcut Alt + F11 to open the Visual Basic Window
- 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
- In the blank module paste the code and close the Visual Basic Editor
- Then use the shortcut Alt + F8 to open the Macro Box. You would have the list of all the macros here
- You would see the Macro that you have just pasted in the Module as ‘CreateIndex’
- Run it. You would see an Index with all the sheet names hyperlinked to the respective sheet.
Other Useful Macros
- Automated Filter with Macro
- Unhiding Multiple Sheets at Once
- Covert Numbers into Indian Currency Words