I have inherited a single Workbook with 125 Tabs
(Worksheets). I'd like to code a macro to "dump" all
sheets names to a newly created worksheet named "Main"
(which I've inserted in the 1st position). The worksheet
names should appear in Main in the Cells A1:A125. How can
I do this?

Jim,

Try the following:

Sub AAA()
Dim WS As Worksheet
Dim Ndx As Long
On Error Resume Next
With Worksheets
Set WS = .Item("MAIN")
If Err.Number < 0 Then
Set WS = .Add
WS.Name = "MAIN"
End If
For Ndx = 1 To .Count
.Item("MAIN").Cells(Ndx, 1).Value = .Item(Ndx).Name
Next Ndx
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Jim May" wrote in message
...
I have inherited a single Workbook with 125 Tabs
(Worksheets). I'd like to code a macro to "dump" all
sheets names to a newly created worksheet named "Main"
(which I've inserted in the 1st position). The worksheet
names should appear in Main in the Cells A1:A125. How can
I do this?

With 125 sheets, a slight variation to Chip's excellent idea would be to
make the names a Hyperlink. Each sheet could hold a description of what
that sheet does. This information could be placed next to the Hyperlink to
help find what you are looking for. If you add the Toolbar button "Web Back
Button" on your Toolbar, then you can use that to jump back to the Table of
Contents. (after viewing your sheet). Just an idea.

Sub SheetsTOC()
Dim WS As Worksheet
Dim Ndx As Long
On Error Resume Next
With Worksheets
Set WS = .Item("MAIN")
If Err.Number < 0 Then
Set WS = .Add(Sheets(1))
WS.Name = "MAIN"
End If
For Ndx = 1 To .Count

WS.Hyperlinks.Add _
Anchor:=.Item("MAIN").Cells(Ndx, 1), _
Address:=vbNullString, _
SubAddress:=.Item(Ndx).Name & "!A1", _
TextToDisplay:=.Item(Ndx).Name
Next Ndx
End With
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Chip Pearson" wrote in message
...
Jim,

Try the following:

Sub AAA()
Dim WS As Worksheet
Dim Ndx As Long
On Error Resume Next
With Worksheets
Set WS = .Item("MAIN")
If Err.Number < 0 Then
Set WS = .Add
WS.Name = "MAIN"
End If
For Ndx = 1 To .Count
.Item("MAIN").Cells(Ndx, 1).Value = .Item(Ndx).Name
Next Ndx
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Jim May" wrote in message
...
I have inherited a single Workbook with 125 Tabs
(Worksheets). I'd like to code a macro to "dump" all
sheets names to a newly created worksheet named "Main"
(which I've inserted in the 1st position). The worksheet
names should appear in Main in the Cells A1:A125. How can
I do this?