Author: Oscar Cronquist Article last updated on September 10, 2019

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. Click the "Macros" button and the Macro dialog box appears.
  4. Select macro CreateToC.
  5. Click "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. Click "Insert" on the menu.
  5. Click "Module" to insert a code module.
  6. Paste VBA code to the code module.
  7. Return to Excel.
Note, save the workbook with file extension *.xlsm to attach the code to your workbook.

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!