MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

excel table of content

  Asked By: Willie    Date: Oct 13    Category: MS Office    Views: 1776

I have a workbook with a lots of sheets. Is there a way to create a sheet
which has a table of contents based on worksheet names so I can quickly click
on the link and go the sheet I want?



8 Answers Found

Answer #1    Answered By: Woodrow Jones     Answered On: Oct 13


Answer #2    Answered By: Iris Sanders     Answered On: Oct 13

Generally you do not need to do this. You can right-click on the arrows at
the left of the sheet  tabs and a list of the sheets  is displayed.

"Not many people know that"

Answer #3    Answered By: Olga Allen     Answered On: Oct 13

Actually, I will prefer to have my workbook  open to this page wich contains a
list of all my sheets. and they are hyperlinked . I click on the name and it
takes me to the selected sheet.

Answer #4    Answered By: Botan Suzuki     Answered On: Oct 13

You can just hyperlink to a place on the spreadsheet by selecting the
"Place in this document" on the hyperlink pop up form. Then when you
make the table  of contents  use the line Worksheets("table of
contents").Activate under the workbook_open sub

Answer #5    Answered By: Fahmida Ahmed     Answered On: Oct 13

My workbook  contains at least 30 sheets. I will like the code to automatically
create the table  of content  for me. I do have a code which does just that but
the problem with this code is that if one of my sheets  name has a space or dash,
the hyperlink for that sheet  won't work. I am fine when I have a new workbook I
can Name he sheet with one word or an underscore, but I inherited some old files
which the names  have no consistency. I pasted the code below.
Thank you for your help again

Sub CreateTable()
Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add _
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 16
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> wsTOC.Name Then
wsTOC.Hyperlinks.Add _
Anchor:=wsTOC.Cells(r, 1), _
Address:="", _
SubAddress:=ws.Name & "!a1", _
r = r + 1
End If
Application.ScreenUpdating = True
End Sub

Answer #6    Answered By: Vidhya Iyer     Answered On: Oct 13

How about making the macro step through all the worksheet  names and change any
spaces to underscores?

Answer #7    Answered By: Cyka Jansen     Answered On: Oct 13

I tried your code (using Excel 2002 on Windows 2000), and it worked
for me. I changed worksheet  names to include spaces and dashes, and
it worked also.

By the way, the code does not return the name of chart sheets.

Answer #8    Answered By: Robin Hayes     Answered On: Oct 13

I have no idea why this code is not working for me. It does not work if the
sheet name has a space or score.

Didn't find what you were looking for? Find more on excel table of content Or get search suggestion and latest updates.