This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, Pivot tables, Excel defined Tables and named ranges.

The hyperlinks allow you to navigate quickly to any of the Excel objects mentioned above.

What you will learn in this article

• Iterate through worksheets in the active workbook.
• Iterate through Pivot Tables in the active worksheet.
• Iterate through Excel defined Tables in the active worksheet.
• Iterate through named ranges in the active workbook.
• Create hyperlinks programmatically.
• Change column width so the content fits.
• Save text value to a cell using VBA.
• Bold cell text programmatically.
• Select the next cell below using VBA.

How to use macro

The animated image above demonstrates the macro.

1. Select a destination cell on a worksheet you want to populate.
2. Go to tab "Developer" on the ribbon.
3. Press with left mouse button on the "Macros" button and the Macro dialog box appears.
4. Select macro CreateToC.
5. Press with left mouse button on "Run" button.
6. The macro creates hyperlinks to all worksheets, pivot tables, Excel defined Tables and named ranges in the active workbook.
7. The macro ends.

VBA code

'Name macro
Sub CreateToC()

'Dimension variables and declare data types
Dim sh As Worksheet
Dim cell As Range
Dim pt As PivotTable
Dim tbl As ListObject
Dim nms As Name

'Populate selected cell with "Table of Contents"
ActiveCell.Value = "Table of Contents"

'MAke the selected cell bolded
ActiveCell.Font.Bold = True

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Save text Worksheets to selected cell
ActiveCell.Value = "Worksheets"

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Iterate through each worksheet in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Make sure the worksheet name is not equal to the currently selected worksheet
    If ActiveSheet.Name <> sh.Name Then

        'Add hyperlink to selected cell with worksheet name linking the the worksheet
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name

        'Select the next cell below
        ActiveCell.Offset(1, 0).Select
    End If
Next sh

'Save text Pivot tables to selected cell
ActiveCell.Value = "Pivot tables"

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Iterate through all worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Iterate through all pivot tables in worksheet
    For Each pt In sh.PivotTables

        'Add hyperlink to selected cell with pivot table name linking to the pivot table
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'!" & pt.TableRange1.Address, TextToDisplay:=pt.Name

        'Select the next cell below
        ActiveCell.Offset(1, 0).Select

    'Continue with next pivot table
    Next pt

'Continue with next worksheet
Next sh

'Save text Tables to selected cell
ActiveCell.Value = "Tables"

'Select the next cell below
ActiveCell.Offset(1, 0).Select


'Iterate through all worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Iterate through all Excel defined Tables in worksheet
    For Each tbl In sh.ListObjects

            'Add hyperlink to selected cell with Excel defined Table name linking to the Excel defined Table
            ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            tbl.Name, TextToDisplay:=sh.Name

            'Select the next cell below
            ActiveCell.Offset(1, 0).Select

    'Continue with next Excel defined Table
    Next tbl

'Continue with next worksheet
Next sh

'Save text Named Ranges to selected cell
ActiveCell.Value = "Named ranges"

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Iterate through all named ranges in workbook
For Each nms In ActiveWorkbook.Names

            'Add hyperlink to selected cell with the name of the named range linking to the the named range
            ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            nms.Name, TextToDisplay:=nms.Name

            'Select the next cell below
            ActiveCell.Offset(1, 0).Select

'Continue with next named range
Next nms

'Change column widths so they fit the content
ActiveSheet.Columns(ActiveCell.Column).AutoFit

End Sub

Where to put the code?

1. Copy above VBA code.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Select your workbook in the Project Explorer.
4. Press with left mouse button on "Insert" on the menu.
5. Press with left mouse button on "Module" to insert a code module.
6. Paste VBA code to the code module.
7. Return to Excel.

Get the Excel file

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

Hyperlinks category

Create links to all sheets in a workbook programmatically
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet.  You will then be able to quickly […]

Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

Named range category

Create a dynamic named range
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

List all named ranges and their cell references
This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]

Excel categories

Comments (3)