Add a table of contents to your workbook – it’s easy, I promise!

Sometimes workbooks can be very large and hard to navigate. Only so many tabs fit across the bottom of the screen, and it’s hard to know how long each worksheet is. Excel doesn’t have a built-in way to add a table of contents to a workbook; however, there is a way! In this post, I’ll show you how to add a new worksheet to the beginning of your workbook called “TOC” (table of contents). This example uses Excel 2010.

On the TOC sheet, Column A lists each sheet name and includes a hyperlink shortcut to the appropriate worksheet. Column B lists the worksheet number (which sheet it is in the workbook) and the number of pages contained on that worksheet (how many printed pages it would be). You have to use VBA (Visual Basic for Applications), but don’t be scared off by the fact that you need code—it’s actually pretty simple, and I’ll walk you through it step-by-step.

First, you have to add the code to your workbook, and to do that you need the Developer tab. If you don't usually work with code in Excel, you probably don't see the Developer tab in the Ribbon. To show the Developer tab:

1. Click the File tab.
2. Under Help, click Options.
3. Click Customize Ribbon.
4. Under Customize the Ribbon, select the Developer check box.

Now you can create a macro:

1. On the Developer tab, in the Code group, click Visual Basic.
   
   
2. In the Visual Basic Editor, on the Insert menu, click Module.
3. In the code window of the module, type or copy the following macro code:

Option Explicit
Sub Create_TOC()
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet

Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long

Set wbBook = ActiveWorkbook

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'If the TOC sheet already exist delete it and add a new
'worksheet.

On Error Resume Next
With wbBook
    .Worksheets("TOC").Delete
    .Worksheets.Add Before:=.Worksheets(1)
End With
On Error GoTo 0

Set wsActive = wbBook.ActiveSheet
With wsActive
    .Name = "TOC"
    With .Range("A1:B1")
        .Value = VBA.Array("Table of Contents", "Sheet # - # of Pages")
        .Font.Bold = True
    End With
End With

lnRow = 2
lnCount = 1

'Iterate through the worksheets in the workbook and create
'sheetnames, add hyperlink and count & write the running number
'of pages to be printed for each sheet on the TOC sheet.
For Each wsSheet In wbBook.Worksheets
    If wsSheet.Name <> wsActive.Name Then
        wsSheet.Activate
        With wsActive
            .Hyperlinks.Add .Cells(lnRow, 1), "", _
            SubAddress:="'" & wsSheet.Name & "'!A1", _
            TextToDisplay:=wsSheet.Name
            lnPages = wsSheet.PageSetup.Pages().Count
            .Cells(lnRow, 2).Value = "'" & lnCount & "-" & lnPages
        End With
        lnRow = lnRow + 1
        lnCount = lnCount + 1
    End If
Next wsSheet

wsActive.Activate
wsActive.Columns("A:B").EntireColumn.AutoFit

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

Now, save your workbook with the macro code.

1. In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel.
2. On the File menu, click Save As, and then save the file as an Excel Macro-Enabled Workbook (.xlsm).

You are almost ready to run the code. You might need to change your macro security settings to enable macros. To set the security level temporarily to enable all macros, do the following:

1. On the Developer tab, in the Code group, click Macro Security.
   
   
2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.
   
   Note    To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

And now you can run the code that creates the table of contents worksheet!

1. On the Developer tab, in the Code group, click Macros.
   
   
2. In the Macro window, select the Create_TOC macro, and click Run.