How to create hyperlinked index of sheets in workbook?
In our daily work, there may be multiple worksheets in a workbook file, how could you create an index of sheet tabs in a worksheet and make a hyperlink for each tab? This article, I will talk about some quick tricks for dealing with this task in Excel.
The following VBA code may help you to create indexes of sheet names in a new worksheet which in front of other worksheets. Please do as follows:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Create hyperlinked index of sheets
Sub CreateIndex() 'updateby Extendoffice Dim xAlerts As Boolean Dim I As Long Dim xShtIndex As Worksheet Dim xSht As Variant xAlerts = Application.DisplayAlerts Application.DisplayAlerts = False On Error Resume Next Sheets("Index").Delete On Error GoTo 0 Set xShtIndex = Sheets.Add(Sheets(1)) xShtIndex.Name = "Index" I = 1 Cells(1, 1).Value = "INDEX" For Each xSht In ThisWorkbook.Sheets If xSht.Name <> "Index" Then I = I + 1 xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name End If Next Application.DisplayAlerts = xAlerts End Sub
3. Then press F5 key to execute this code, and a new worksheet named “Index” is inserted into the workbook before all sheets which including sheet names with hyperlinks. See screenshot:
Excepted the above VBA code, you can also finish this job by using a useful tool- Kutools for Excel, with its Create List of Sheet Names, you can quickly create an index of sheet names with hyperlinks or buttons.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do with the following steps:
1. Click Kutools Plus > Worksheet > Create List of Sheet Names, see screenshot:
2. In the Create List of Sheet Names dialog box, please do the following options:
- Choose one index style, you can use hyperlinks or buttons for the sheet names index；
- Specify a name for the sheet which will list sheet index as you need;
- Specify the location where to put the sheet index;
- Select the number of columns you want to put the list of sheet names.
3. After finishing the settings, click Ok button, a new worksheet with sheet name index has been created as follows:
|Sheet names with hyperlinks||Sheet names by using buttons|
Kutools for Excel’s Navigation utility also can help you to list all sheet names in a left pane, and you can click any sheet name to go to the worksheet.
After installing Kutools for Excel, please do as follows:
1. Click Kutools > Navigation, see screenshot:
2. Then click Workbook & Sheet icon to expand the navigation pane:
|1. All opened workbooks are listed here.|
|2. The visible worksheets within current workbook are listed into the below
list box, you can go to the specific sheet by click it from the pane.