Table of Contents in Excel with VBA

Hi all. Again, SO SORRY I haven't written. This week has been crazy preparing for the wedding and whatnot. Anyway, wanted to drop everyone a quick line before I go away this weekend for the honeymoon since I haven't written in over a week. I saw a response in the technology section of the JoA about creating a table of contents in Excel. It shows you how you can (quite easily) create a link to another part of the workbook using only Excel. It can be quite useful to link different parts of the document (for example, to link a summary sheet to the detailed breakdown). Read the article to get the gist of how this can be accomplished. I only want to take it a little further to help summarize MANY more tabs (maybe 20 or more?).

To do this, we're going to use VBA. Click here to get a brief overview of VBA, or simply read on as this won't be too complicated. In the article, the author describes how to create a link so we won't repeat it here. But, instead of just creating the link, record a macro first and stop it after the link has been created. You can view the VBA code behind how Excel created the link by hitting Alt-F11 and finding the module where you saved the macro. Once you look this over, you'll see that it adds a hyperlink with some specific information. We can customize what Excel did to come up with this:

Sub Create_TCs()
Set toc = ActiveWorkbook.Sheets("TOCs") ' Place to store the links
i = 1 ' Which cell are we on in the TOCs sheet?  (A1, A2, A3 ... etc.)

For Each s In ActiveWorkbook.Sheets
    If s.Name <> "TOCs" Then ' Don't create a link to the TOCs sheet
        toc.Hyperlinks.Add Anchor:=toc.Range("A" & i), Address:="", _
            SubAddress:="'" & s.Name & "'!A1", TextToDisplay:=s.Name
        i = i + 1 ' Increment i so that we go to the next cell on the TOCs sheet
    End If
Next s
End Sub

You can copy the code above to your personal macro workbook (click here to learn about this workbook) and then create a new custom button on your toolbar to automatically call the procedure. It shouldn't be hard to follow. All it does is loop through all the sheets in the active workbook, and creates a link on the "TOCs" tab to all the other sheets in the workbook. NOTE: The "TOCs" tab must already be created before the macro is run; otherwise, you'll get an error.

I hope this helps. If you ever have a workbook with more than 10 or 20 tabs and you want a table of contents page, simply insert a new sheet (Shift-F11) and rename it to "TOCs", then run the macro. All the other sheets will be linked in automatically. Good luck!