Table of Contents

Laurel Jordan

New Member
Joined
May 7, 2002
Messages
1
How do I add a table of contents to an Excel Workbook? I have a workbook that contains many worksheets (50+) and I want an easy way to access each tab without having to scroll.
This message was edited by Laurel Jordan on 2002-05-08 12:45
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I use an index page zIndex.

Sub ListSheets()
' List includes a hyperlink for each sheet
' - sorts the list
' - includes description from H1 of each Sheet
' - includes File Name in Column C
' - currently doesn't allow spaces in sheetnames
' This is OK since I do not put spaces in sheet names since
' such spaces create problems with a variety of formulas.
Dim i As Integer
Dim cRow As Long
Application.ScreenUpdating = False
cRow = 2
For i = 1 To ActiveWorkbook.Sheets.Count
Cells(cRow - 1 + i, 3) = "'" & Application.ActiveWorkbook.Name
Cells(cRow - 1 + i, 4) = "'" & Sheets(i).Name
'Cells(cRow - 1 + i, 2).Value = "=indirect(address(1,8,,,rSheetNames))" ' see next line
Cells(cRow - 1 + i, 2).Value = "=INDIRECT(RC[2]&""!""&CELL(""address"",R1C8))"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(cRow - 1 + i, 4), Address:="", SubAddress:=Sheets(i).Name & "!A1"
Next
Cells(cRow - 1 + i, 4).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B1") = "Description"
Range("C1") = "File Name"
Range("D1") = "Sheet Names"
Rows("1:1").Font.Bold = True
With Range("A1").CurrentRegion
.Columns.AutoFit
.Font.Size = 12
End With
Application.ScreenUpdating = True
End Sub
This message was edited by Dave Patton on 2002-05-08 11:23
 
Upvote 0
Opt1- Create custom views and insert custom view drop down box on menu bar. Then just select desired sheet from drop down

Opt2- Instert "name box" onto tool bar and type in the Sheetname and a cell ie. Sheet50!A1

Opt3- Create a series of links from on reference page ... for details look up "create shortcuts to worksheets" in excel help

Opt4- Create a reference sheet with fifty command buttons with one line macro saying what page to select
 
Upvote 0
If you right-click on the tab section of the horizontal scroll bar (left end), it shows a ready-made "TOC"; clicking on the one you want should take you there...
 
Upvote 0
On 2002-05-08 11:33, CompTutor wrote:
If you right-click on the tab section of the horizontal scroll bar (left end), it shows a ready-made "TOC"; clicking on the one you want should take you there...

Hi CompTutor:
This will work only if the SheetTabs option is activated in TOOLS|VIEW|WINDOW_OPTIONS|
In some cases the developer of the workbook may choose to not show the sheets.

Regards!
 
Upvote 0
Yogi Anand, thanks for that clarification. (I started hanging here because I discovered that I do NOT know how things are used in the "real" world, and I appreciate your response.)
 
Upvote 0
On 2002-05-08 11:45, Yogi Anand wrote:
On 2002-05-08 11:33, CompTutor wrote:
If you right-click on the tab section of the horizontal scroll bar (left end), it shows a ready-made "TOC"; clicking on the one you want should take you there...

Hi CompTutor:
This will work only if the SheetTabs option is activated in TOOLS|VIEW|WINDOW_OPTIONS|
In some cases the developer of the workbook may choose to not show the sheets.

Regards!

How do I add a table of contents to an Excel Workbook? I have a workbook that contains many worksheets (50+) and I want an easy way to access each tab without having to scroll.

However, if they didn't have worksheet tabs they wouldn't have the tab scrolling buttons either.
 
Upvote 0
On 2002-05-08 15:48, CompTutor wrote:
Yogi Anand, thanks for that clarification. (I started hanging here because I discovered that I do NOT know how things are used in the "real" world, and I appreciate your response.)

Hi CompTutor:
Your contribution was a great one -- I just tagged on to it to enhance its value and not to diminish from it. I am glad you took my comment in the positive light.
And, I believe what Mark W is saying -- hey, if the Sheet Tabs are there, let us make use of them as you proposed.

Regards!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top