Auto-generate a Table of Contents in Excel
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:
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 > 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 & "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 & " " Else wSheet.Cells(TableRow, 2).Value = PageCount + 1 & " - " & 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 is really really useful, thanks!
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.
I found useful code usage here — that I couldn't even find on Usenet. Nice work.
Definately saved me a few hours so thanks a lot
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
This is simply wonderful!!!
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
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?