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