You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Sometimes workbooks can be very large and hard to navigate. Only so many tabs fit across the bottom of the screen, and it’s hard to know how long each worksheet is. Excel doesn’t have a built-in way to add a table of contents to a workbook; however, there is a way! In this post, I’ll show you how to add a new worksheet to the beginning of your workbook called “TOC” (table of contents). This example uses Excel 2010.
On the TOC sheet, Column A lists each sheet name and includes a hyperlink shortcut to the appropriate worksheet. Column B lists the worksheet number (which sheet it is in the workbook) and the number of pages contained on that worksheet (how many printed pages it would be). You have to use VBA (Visual Basic for Applications), but don’t be scared off by the fact that you need code—it’s actually pretty simple, and I’ll walk you through it step-by-step.
First, you have to add the code to your workbook, and to do that you need the Developer tab. If you don't usually work with code in Excel, you probably don't see the Developer tab in the Ribbon. To show the Developer tab:
Now you can create a macro:
Option ExplicitSub Create_TOC()Dim wbBook As WorkbookDim wsActive As WorksheetDim wsSheet As Worksheet
Dim lnRow As LongDim lnPages As LongDim lnCount As Long
Set wbBook = ActiveWorkbook
With Application .DisplayAlerts = False .ScreenUpdating = FalseEnd With
'If the TOC sheet already exist delete it and add a new'worksheet.
On Error Resume NextWith wbBook .Worksheets("TOC").Delete .Worksheets.Add Before:=.Worksheets(1)End WithOn Error GoTo 0
Set wsActive = wbBook.ActiveSheetWith wsActive .Name = "TOC" With .Range("A1:B1") .Value = VBA.Array("Table of Contents", "Sheet # - # of Pages") .Font.Bold = True End WithEnd With
lnRow = 2lnCount = 1
'Iterate through the worksheets in the workbook and create'sheetnames, add hyperlink and count & write the running number'of pages to be printed for each sheet on the TOC sheet.For Each wsSheet In wbBook.Worksheets If wsSheet.Name <> wsActive.Name Then wsSheet.Activate With wsActive .Hyperlinks.Add .Cells(lnRow, 1), "", _ SubAddress:="'" & wsSheet.Name & "'!A1", _ TextToDisplay:=wsSheet.Name lnPages = wsSheet.PageSetup.Pages().Count .Cells(lnRow, 2).Value = "'" & lnCount & "-" & lnPages End With lnRow = lnRow + 1 lnCount = lnCount + 1 End IfNext wsSheet
With Application .DisplayAlerts = True .ScreenUpdating = TrueEnd WithEnd Sub
Now, save your workbook with the macro code.
You are almost ready to run the code. You might need to change your macro security settings to enable macros. To set the security level temporarily to enable all macros, do the following:
And now you can run the code that creates the table of contents worksheet!
Is there somewhere where we can copy the code from?
Now how exactly are we supposed to copy this code when it is a picture rather than text?
I would advise people to save this to a separate workbook so they can keep it and reuse it. It shouldn't live in the target workbook, especially if it is the only code there and users have macro warnings popping up every time they open it.
Also, I wouldn't include the number of printed pages (I've never needed that in 20 years and I don't think most people would, and juxtaposing it with sequence numbers is visually confusing) and I'd put the numbered list on the left, as numbers normally precede labels.
Finally, I wouldn't destroy the sheet each time you run it, because TOC sheets often contain other things like instructions, and if someone reruns the macro after adding some sheets, whooosh and all their work disappears.
So I don't think this particular implementation is particularly useful in real world offices.
dermotb, you have it right for the most part. After cleaning up the code according to your comments, I would probably add a button to the TOC page somewhere that simply states "Refresh TOC" that runs the code.
Where can we copy the code from? In trying to type it out, I can not see some of the characters. Would really love to test this out as it would make some of my daily life MUCH easier
Sorry, is there nothing else to talk about?
This is the third time this topic has been discussed in the last year on this blog. Look at the April 2010 archives. The code above was written by Dennis Wallentin and posted on the blog on April 30th. If anyone wants to copy the code, just copy and paste it from there.
When working with a list of data containing last names, is there a way to group them alphabetically using "Group Field" rather than grouping them one letter at a time by hand?
This may be a bit off topic ... but I haven't been able to find an answer to this question: Is there a way to "fill-down" a series of http hyperlinked page addresses through a range of cells?
I have a spreadsheet that I use to track bills introduced in congress.
Col. A: Bill Number (which is hyperlinked to the bill number on THOMAS-Library of Congress)
Col. B: Description of the bill that's been introduced
Col. C: Name of the Congress person introducing the bill
Col. D: Date introduced .... followed by other tracking columns of data about the bill on that row
I'd like to be able to fill-down populated hyperlinked cells in Col. A, but I haven't found a way to do that and end up filling down the Bill numbers and then having to go in and manually create each http hyperlink.
I'm sure there's probably some process for doing it. Just like the incrementation of bill numbers, H.R. 100, H.R. 101, H.R. 102, etc., the hyperlink http addresses also increment:
Could you please consider authoring an article that would describe how one might be able to 'fill-down' and incremented series of http hyperlinked cells?
Diego - you may think that a lousy typist, reading a too small screen capture that is made harder to read by the proofing marks, and someone who is not familiar with VBA and so is typing unfamiliar words, may find this "easy".
If so, please share whatever "screw reality" drugs you are on.