Macro 28: Create a Table of Contents for Your Worksheets
Outside of sorting worksheets, creating a table of contents for the worksheets in a workbook is the
most commonly requested Excel macro. The reason is probably not lost on you. We often have
to work with files that have more worksheet tabs than can easily be seen or navigated. A table of
contents definitely helps.
The following macro not only creates a list of worksheet names in the workbook, but it also ads
hyperlinks so that you can easily jump to a sheet with a simple click.
How it works
It’s easy to get intimidated when looking at this macro. There is a lot going on here. However, if you
step back and consider the few simple actions it does, it becomes a little less scary:
➤ It removes any previous Table of Contents sheet
➤ It creates a new Table of Contents sheet
➤ It grabs the name of each worksheet and pastes it on the Table of Contents
➤ It adds a hyperlink to each entry in the Table of Contents
That doesn’t sound so bad. Now look at the code:
Sub Macro28()
‘Step 1: Declare Variables
Dim i As Long
‘Step 2: Delete Previous TOC if Exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets(“Table Of Contents”).Delete
Application.DisplayAlerts = True
On Error GoTo 0
‘Step 3: Add a new TOC sheet as the first sheet
ThisWorkbook.Sheets.Add _
Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = “Table Of Contents”
‘Step 4: Start the i Counter
For i = 1 To Sheets.Count
‘Step 5: Select Next available row
ActiveSheet.Cells(i, 1).Select
‘Step 6: Add Sheet Name and Hyperlink
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(i, 1), _
Address:=””, _
SubAddress:=”‘” & Sheets(i).Name & “‘!A1”, _
TextToDisplay:=Sheets(i).Name
‘Step 7: Loop back increment i
Next i
End Sub
1. Step 1 declares an integer variable called i to serve as the counter as the macro iterates
through the sheets.
Note that this macro is not looping through the sheets the way previous macros in this Part
did. In previous macros, we looped through the worksheets collection and selected each
worksheet there. In this procedure, we are using a counter (our i variable). The main reason
is because we not only have to keep track of the sheets, but we also have to manage to enter
each sheet name on a new row into a table of contents. The idea is that as the counter pro-
gresses through the sheets, it also serves to move the cursor down in the table of contents so
each new entry goes on a new row.
2. Step 2 essentially attempts to delete any previous sheet called Table of Contents. Because
there may not be any Table of Contents sheet to delete, we have to start Step 2 with the On
Error Resume Next error handler. This tells Excel to continue the macro if an error is
encountered here. We then delete the Table of Contents sheet using the DisplayAlerts
method, which effectively turns off Excel’s warnings so we don’t have to confirm the deletion.
Finally, we reset the error handler to trap all errors again by entering On Error GoTo 0.
3. In Step 3, we add a new sheet to the workbook using the Before argument to position the
new sheet as the first sheet. We then name the sheet Table of Contents. As we mentioned
previously in this Part, when you add a new worksheet, it automatically becomes the active
sheet. Because this new sheet has the focus throughout the procedure, any references to
ActiveSheet in this code refer to the Table of Contents sheet.
4. Step 4 starts the i counter at 1 and ends it at the maximum count of all sheets in the work-
book. Again, instead of looping through the Worksheets collection like we’ve done in previ-
ous macros, we are simply using the i counter as an index number that we can pass to the
Sheets object. When the maximum number is reached, the macro ends.
5. Step 5 selects the corresponding row in the Table of Contents sheet. That is to say, if the i
counter is on 1, it selects the first row in the Table of Contents sheet. If the i counter is at 2,
it selects the second row, and so on.
We are able to do this using the Cells item. The Cells item provides an extremely handy
way of selecting ranges through code. It requires only relative row and column positions as
parameters. So Cells(1,1) translates to row 1, column 1 (or cell A1). Cells(5, 3)
translates to row 5, column 3 (or cell C5). The numeric parameters in the Cells item are
particularly handy when you want to loop through a series of rows or columns using an
incrementing index number.
6. Step 6 uses the Hyperlinks.Add method to add the sheet name and hyperlinks to the
selected cell. This step feeds the Hyperlinks.Add method the parameters it needs to
build out the hyperlinks.
7. The last step in the macro loops back to increment the i counter to the next count. When
the i counter reaches a number that equals the count of worksheets in the workbook, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
