Creating an Excel TOC

[Guest]

confused

I followed the directions from Gord to the t and when I went to
Tools-Marco-Macros the macro did not show up in the list. Am I missing a
step? Please help.
I too am trying to build a TOC that links to the sheet.
****************************************

Here is a macro that will list all sheets onto a new sheet.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Worksheets.Add
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

 

[Bob Phillips]

Yes, it won't show if you declare it as Private. Make it Public and you
should see it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Anne Troy]

A private sub doesn't work, Brad. You need to change the name to Sub
ListSheets() or run the Private Sub from a regular Sub.
************
Anne Troy
www.OfficeArticles.com

 

[Guest]

Bob this worked great. My next questionis how to make them hyperlinks. Any
suggestions?

 

[Bob Phillips]

Public Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Dim sheet As Object

Worksheets.Add
Set Rng = Range("A1")
For Each sheet In ActiveWorkbook.Sheets
If sheet.Name <> ActiveSheet.Name Then
Rng.Offset(i, 0).Value = sheet.Name
ActiveSheet.Hyperlinks.Add Anchor:=Rng.Offset(i, 0), _
Address:="", _
SubAddress:="'" & sheet.Name & "'!A1", _
TextToDisplay:=sheet.Name
i = i + 1
End If
Next sheet
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)