Table of Contents
In this article we will learn how to create list of all the sheets that exist in your excel workbook. You can click on the sheet name to go that sheet.
Steps to insert code
- Open the Visual Basic Editor using Alt + F11
- Goto Insert Tab
- Click on Module
- Paste the below code into module
Code
Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> sh.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
ActiveCell.Offset(1, 0).Select
End If
Next sh
MsgBox "Index is created", vbOKOnly, "Awesome Analytics"
End Sub
Import Code into your file
You can directly import the above code into your Excel file by following the below steps:
- Download the code file from here
- Open the Visual Basic Editor using Alt + F11
- Goto File Menu
- Click on Import File
- Browse to the folder path where the downloaded file is saved
- Click on Open
- The code is now available in your file.
Run Macro in Download File
- Go the sheet in which Index should be generated. List will be generated starting from the current cell of the active sheet.
- Go to Developer Tab –> Macros
- Click on CreateLinksToAllSheets
- Click on Run
This is how we can create a list of all the sheets along with the links to navigate easily in the excel workbook.
