Welcome to Excel Jet Consult blog post. This tutorial focused on how to List and Hyperlink Sheets in Excel using VBA.
Question to Solve
In the caption below, we have several sheet tabs. What we want to do is essentially to write VBA code that will list and hyperlink all the sheet tabs in the Home Page sheet
Solution
- On the keyboard, deploy ALT + F11 or in the Code group of the Developer tab, click on Visual Basic
- Select Module from the Insert dropdown as seen in the caption below
- In the code window, write the following lines of code
Sub list_sheet_tabs()
Dim iList As Worksheet
For Each iList In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> iList.Name Then
ActiveCell.Hyperlinks.Add anchor:=Selection, Address:=””, SubAddress:=”‘” & iList.Name & “‘” & “!A1”, _
TextToDisplay:=iList.Name
Worksheets(iList.Name).Hyperlinks.Add anchor:=Worksheets(iList.Name).Range(“A1″), Address:=””, _
SubAddress:=”‘” & ActiveSheet.Name & “‘” & “!A1″, TextToDisplay:=”sample”
ActiveCell.Offset(1, 0).Select
End If
Next iList
End Sub
Run Code
The moment we run the code, all the sheet tabs will be listed and hyperlinked in the Home Page while in cell A1 of other sheet tabs, we will see Back Home hyperlink which we can click on to return to the Home Page
- To run the code, deploy F5 on the keyboard or click on the green play icon
In the caption below, we can all the sheet tabs listed in the Home Page
Video
You can watch the video below and subscribe to my YouTube Channel for more videos tutorials