Auto-generate a Table of Contents in Excel

Jan 14th 2004
8 Comments

Respond
Trackback

Here’s a cool, handy macro to auto-generate a Table of Contents for any Excel file.

Here’s a cool, handy macro that will auto-generate a Table of Contents for any Excel file.

Steps remains the same as in any macro (Alt F11 to start VBA, Insert module, paste the code, save, File -> Return to Excel, then Alt F8, and Run). That’s a mouthful, but you know what to do.

Here’s the code:

<strong>Sub GenerateTableOfContents()</strong>
' Does a TOC already exist?
' If Err system variable is &gt; 0, it doesn't
Dim wSheet As Worksheet
On Error Resume Next
Set wSheet = Worksheets("Table of Contents")
If Not Err = 0 Then
 
' The Table of contents doesn't exist. Add it
Set wSheet = Worksheets.Add(Before:=Worksheets(1))
wSheet.Name = "TOC"
End If
On Error GoTo 0
 
' Set up the table of contents page
wSheet.[A2] = "Table of Contents"
 
With wSheet.[A6]
.CurrentRegion.Clear
.Value = "Subject"
End With
 
wSheet.[B6] = "Page(s)"
wSheet.Range("A1:B1").ColumnWidth = Array(36, 12)
TableRow = 7
PageCount = 0
Worksheets.Select
displayMessage = "We'll do a Print Preview for some calculations."
displayMessage = displayMessage &amp; "Please 'Close' the window when it appears."
MsgBox displayMessage
ActiveWindow.SelectedSheets.PrintPreview
 
' Now loop thru sheets, collecting TOC info
For Each S In Worksheets
S.Select
ThisName = S.Name
HPages = S.HPageBreaks.Count + 1
VPages = S.VPageBreaks.Count + 1
ThisPages = HPages * VPages
 
' Enter info about this sheet on TOC
wSheet.Cells(TableRow, 1).Value = ThisName
wSheet.Cells(TableRow, 2).NumberFormat = "@"
If ThisPages = 1 Then
  wSheet.Cells(TableRow, 2).Value =
  PageCount + 1 &amp; " "
Else
  wSheet.Cells(TableRow, 2).Value =
  PageCount + 1 &amp; " - " &amp; PageCount + ThisPages
End If
PageCount = PageCount + ThisPages
TableRow = TableRow + 1
Next S
<strong>End Sub</strong>

That’s all there is to it! Note that VBA does not allow putting the lines after an equal to sign (” = “) on a separate line, although the above code has them so (only to save formatting.)

Feel free to leave a note if you don’t understand some bit of the code and I’ll try to explain, though it’s quite self-explanatory. The calculation of the number of pages is done through the number of page breaks inside the Print Preview.

Have fun!

This post is tagged Excel, Microsoft, Office, Stash/Code

8 Comments

Jan 18th, 2004

Emmanuel

This is really really useful, thanks!
Jan 20th, 2004

toto

IN VBA code you can continue on more than one lines with the underbar chatacter.

_

E.g.,

displayMessage = _
“We’ll do a Print Preview for some _
calculations.” _
displayMessage = displayMessage & _
“Please ‘Close’ the window when it _
appears.”

This should work. Just like in Visual Basic.
May 1st, 2005

A nondescript Googler

I found useful code usage here - that I couldn’t even find on Usenet. Nice work.
May 15th, 2005

John

Definately saved me a few hours so thanks a lot
Jun 17th, 2005

mark

I am getting a compile error on the wsheet function. i am using excel 2003 fully service packed. what am i doing wrong?

If ThisPages = 1 Then
wSheet.Cells(TableRow, 2).Value = PageCount + 1&” ”
Else
wSheet.Cells(TableRow, 2).Value = PageCount + 1&” - “&PageCount + ThisPages
Sep 23rd, 2006

Rachelle

This is simply wonderful!!!
Nov 27th, 2006

Launcher

I am running Excel 2000 with Visual Basic 6.0

When I run this code I get the following error:
Compile Error:
Syntax Error

Plus, the following line is highlighted:
’ Does a TOC already exist?

Also, there are a number of lines that are displayed in red text.

Can someone please help…I know nothing about codes, Visual Basic nor macros
Mar 28th, 2007

Jason

The program works. Thank you. I, however, constantly get an additional sheet added prior to the TOC page (e.g. Sheet 1) and it is listed on the TOC as page 1. Is there a way to alter the code to eliminate this from happening?

Sniptools