ExtremeCPA - Technology for Business

Thursday, August 11, 2005

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!

3 Comments:

  • Extraordinary blog. Your site was hip and fresh
    and we'll visit it again! I love surfing the internet
    for blogs.
    Go and find my 3220 accessory cell nokia phone blog.

    By Blogger Home Wiring Articles, at 4:14 PM  

  • truly amazing thanks a lot

    By Anonymous kelly, at 4:53 AM  

  • This works great. Wish I found this simple piece of code a year ago.

    Is there any way to add page numbers to the table of contents that correspond to those in a pdf file created from the workbook?

    all the best

    Geoff

    By Anonymous Geoff Smith, at 3:41 AM  

Post a Comment

<< Home