Create a List of All Sheet Names | Dynamic Excel Table of Contents inc Hyperlinks & Auto Updates

In this video I demonstrate how to create a list of worksheet names in Excel that you can use as a table of contents. I will show you how to add hyperlinks so that you can easily navigate inside your workbook. The worksheet list (or table of contents) will be dynamic, so if you add or delete sheets or change sheet names the table of contents will automatically update.

This video features two methods for creating a list of sheet names. The first method uses a VBA macro ( see above for a link to the code ) and the second uses an Excel 4.0 macro function called GET.WORKBOOK. VBA is more secure to use than the old Excel 4.0 macros, so if you are worried about security, use the VBA solution.

Click here to download the featured file.

Here’s a copy of the the featured code:

Dim sht As Worksheet
Dim TOCsht As Worksheet
Dim RowNo As Integer

Set TOCsht = Sheet11
TOCsht.Cells.Clear

With TOCsht.Cells(1, 1)
    .Value = "Table of Contents"
    .Font.Bold = True
    .Font.Size = 11
    .Font.Color = vbWhite
    .Interior.Color = RGB(68, 114, 196)
End With

RowNo = 1

For Each sht In ThisWorkbook.Worksheets
    If sht.CodeName <> "Sheet11" Then
        RowNo = RowNo + 1
        TOCsht.Cells(RowNo, 1).Hyperlinks.Add _
        Anchor:=Cells(RowNo, 1), _
        Address:="", SubAddress:="'" & sht.Name & "'!A1", _
        ScreenTip:="", _
        TextToDisplay:=sht.Name
    End If
Next sht

Columns.AutoFit

Video Table of Contents

00:00 – Intro

00:55 – VBA Method

08:57 – GET.WORKBOOK METHOD