14 Jan, 2004
Miscellaneous
Share

Auto-generate a Table of Contents in Excel

Here's a cool, handy macro to auto-generate a Table of Con­tents for any Excel file.

Here's a cool, handy macro that will auto-generate a Table of Con­tents for any Excel file.

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

Here's the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<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 sep­a­rate line, although the above code has them so (only to save formatting.)

Feel free to leave a note if you don't under­stand some bit of the code and I'll try to explain, though it's quite self-explanatory. The cal­cu­la­tion of the num­ber of pages is done through the num­ber of page breaks inside the Print Preview.

Have fun!

TOPICS

  • Emmanuel

    This is really really use­ful, thanks!

  • toto

    IN VBA code you can con­tinue on more than one lines with the under­bar chatacter.

    _

    E.g.,

    dis­playMes­sage = _
    "We'll do a Print Pre­view for some _
    cal­cu­la­tions." _
    dis­playMes­sage = dis­playMes­sage & _
    "Please 'Close' the win­dow when it _
    appears."

    This should work. Just like in Visual Basic.

  • A non­de­script Googler

    I found use­ful code usage here — that I couldn't even find on Usenet. Nice work.

  • John

    Defi­nately saved me a few hours so thanks a lot

  • mark

    I am get­ting a com­pile error on the wsheet func­tion. i am using excel 2003 fully ser­vice packed. what am i doing wrong?

    If This­Pages = 1 Then
    wSheet.Cells(TableRow, 2).Value = Page­Count + 1&" "
    Else
    wSheet.Cells(TableRow, 2).Value = Page­Count + 1&" — "&Page­Count + ThisPages

  • Rachelle

    This is sim­ply wonderful!!!

  • Launcher

    I am run­ning Excel 2000 with Visual Basic 6.0

    When I run this code I get the fol­low­ing error:
    Com­pile Error:
    Syn­tax Error

    Plus, the fol­low­ing line is high­lighted:
    ’ Does a TOC already exist?

    Also, there are a num­ber of lines that are dis­played in red text.

    Can some­one please help…I know noth­ing about codes, Visual Basic nor macros

  • Jason

    The pro­gram works. Thank you. I, how­ever, con­stantly get an addi­tional 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 elim­i­nate this from happening?

  • ashish mehra

    If you want to know more about "How to Insert Page Num­bers in Microsoft Excel", check this link .….…

    http://www.exceltip.com/tips/how-to-insert-page-numbers-in-microsoft-excel.html

© 2010 —Sniptools. All Rights Reserved.