A week ago I posted Create links to all sheets in a workbook and today I want to show you how to create links to all:

• Worksheets
• Pivot tables
• Tables
• Named ranges

in a workbook.

How to use

1. Select a cell
2. Go to tab "Developer"
3. Click "Macros" button
4. Select CreateToC
5. Click "Run" button

VBA code

Sub CreateToC()
Dim sh As Worksheet
Dim cell As Range
Dim pt As PivotTable
Dim tbl As ListObject
Dim nms As Name
ActiveCell.Value = "Table of Contents"
ActiveCell.Font.Bold = True
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Worksheets"
ActiveCell.Offset(1, 0).Select
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
ActiveCell.Value = "Pivot tables"
ActiveCell.Offset(1, 0).Select
For Each sh In ActiveWorkbook.Worksheets
    For Each pt In sh.PivotTables
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'!" & pt.TableRange1.Address, TextToDisplay:=pt.Name
        ActiveCell.Offset(1, 0).Select
    Next pt
Next sh
ActiveCell.Value = "Tables"
ActiveCell.Offset(1, 0).Select
For Each sh In ActiveWorkbook.Worksheets
    For Each tbl In sh.ListObjects
            ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            tbl.Name, TextToDisplay:=sh.Name
            ActiveCell.Offset(1, 0).Select
    Next tbl
Next sh
ActiveCell.Value = "Named ranges"
ActiveCell.Offset(1, 0).Select
For Each nms In ActiveWorkbook.Names
            ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            nms.Name, TextToDisplay:=nms.Name
            ActiveCell.Offset(1, 0).Select
Next nms
ActiveSheet.Columns(ActiveCell.Column).AutoFit
End Sub

Download excel *.xlsm file

Quickly create links to sheets pivot tables tables and named ranges.xlsm

Related posts:

Create links to all sheets in a workbook

Consolidate sheets in excel, part 2

Consolidate sheets in excel (vba)

Quickly create new sheets (vba)

List all tables and corresponding headers in a workbook (vba)