How to Create a Table of Contents for All Spreadsheets Tab Name with Hyperlinks in Excel

A table of contents might be a useful tool if you're working with a big workbook with lots of sheets and want an organised approach to browse through them. Each tab name can be made into a hyperlink so that you can easily navigate to a certain sheet with a single click. This tutorial will show you step-by-step how to create an Excel table of contents that will automatically update as you add or remove sheets. We'll also demonstrate how to put up hyperlinks that take you straight to the appropriate sheets.

Whether you're a beginner or have some experience with Excel, this tutorial will provide you with the knowledge and skills needed to efficiently manage your spreadsheets. So let's dive in and learn how to create a table of contents with hyperlinks for all spreadsheet tab names in Excel!

Create a Table of Contents for All Spreadsheets Tab Name with Hyperlinks

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can create a table of contents for all spreadsheet tab names with hyperlinks in Excel.

Step 1

Consider an Excel workbook where you have multiple sheets.

First use Alt + F11 to open the VBA application.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Example

Sub CreateTableofcontents()
   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("Table of contents").Delete
   On Error GoTo 0
   Set xShtIndex = Sheets.Add(Sheets(1))
   xShtIndex.Name = "Table of contents"
   I = 1
   Cells(1, 1).Value = "Table of contents"
   For Each xSht In ThisWorkbook.Sheets
      If xSht.Name <> "Table of contents" Then
         I = I + 1
         xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
      End If
   Next
   Application.DisplayAlerts = xAlerts
End Sub

Step 3

Then click F5 to complete the task. Then you can see that the table of contents will be created.

This is how you can create a table of contents for all spreadsheet tab names with hyperlinks in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create a table of contents for all spreadsheet tab names with hyperlinks in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 12-Jul-2023

43 Views

Kickstart Your Career

Get certified by completing the course

Get Started