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)


"Brad" wrote in message
...
Bob this worked great. My next questionis how to make them hyperlinks.
Any
suggestions?

"Bob Phillips" wrote:

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)


"Brad" wrote in message
...
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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.