If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.
Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.
This macro will create a new sheet at the start of the workbook named table of contents. If one already exists it will remove it. It will then list the names of all the sheets in the workbook and insert a hyperlink for each one.
Some of the techniques used within this macro include;
- Using a counter loop on the sheets collection to loop through all the sheets of the workbook
- Create and name a sheet during the macro
- Insert hyperlinks for easier navigation of workbook content
- Concatenation of strings for building the hyperlink address and working with a dynamic range
- The ActiveCell object with Offset to move cells
Create a Table of Contents Macro Code
The VBA code is displayed below. Copy and paste it into the module of a workbook where you need to create a table of contents. This macro is designed to work in a file no matter how many sheets it contains, or what they are called.
Sub CreateTOC() Dim i As Byte Const SheetName = "Table of Contents" With Application .ScreenUpdating = False .DisplayAlerts = False End With If Sheets(1).Name = SheetName Then Sheets(SheetName).Delete End If Sheets.Add Before:=Sheets(1) Sheets(1).Name = SheetName Range("B2").Value = SheetName With Range("B2").Font .Name = "Calibri" .Size = 14 .Underline = xlUnderlineStyleSingle .Bold = True End With Range("B4").Select 'Loop through each sheet and create a table of contents using each sheet name For i = 2 To Sheets.Count ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheets(i).Name & "!A1", TextToDisplay:=i - 1 & ". " & Sheets(i).Name ActiveCell.Offset(2, 0).Select Next Range("B4:B" & ActiveCell.Row).Font.Underline = xlUnderlineStyleNone With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub