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?

Thanks Guys, it works great; How can I now in Cells
B1:B125 use the Indirect() funtion to point to say my A2
cell and bring back Cell $C$3 on that sheetname admin (A2
= Admin)?

-----Original Message-----
Sub ListNames()

Dim ws As Integer

For ws = 1 To Worksheets.Count
Worksheets("Main").Cells(ws, 1) = Worksheets(ws).Name
Next
End Sub

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------
------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------
------------------



"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 your name in A2, in B2:-

=INDIRECT("'"&A2&"'!$C$3") and copy down

This slightly overkill if you have no spaces in your sheet names, but better safe than sorry.

If you didn't have any spaces you could just get away with:-

=INDIRECT(A2&"!$C$3")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Jim May" wrote in message ...
Thanks Guys, it works great; How can I now in Cells
B1:B125 use the Indirect() funtion to point to say my A2
cell and bring back Cell $C$3 on that sheetname admin (A2
= Admin)?

-----Original Message-----
Sub ListNames()

Dim ws As Integer

For ws = 1 To Worksheets.Count
Worksheets("Main").Cells(ws, 1) = Worksheets(ws).Name
Next
End Sub

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------
------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------
------------------



"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?

Doug,

The default method of a collection object is the Item method, so the two
lines of code

Coll("Key")
' and
Coll.Item("Key")

are equivalent. Because I used a With Worksheets statement in the code, I
already have a reference to the Worksheets collection, and therefore use
..Item("Main") to access that particular member of the collection.

used it rather than just Worksheets("MAIN") and Worksheets(Ndx).

Its is because of the With Worksheet statement. I already have a reference
to Worksheets, so all that is necessary to get a member of the collection is
..Item().


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


"Doug Glancy" wrote in message
...
Chip,

I'm very interested in your use of the Item property below. I've never
noticed this property before. I think I see how it allows more
flexibility
in testing for "MAIN" below and then indexing through the sheets, but I'd
love to hear more about how to use this generally, and in this case why
you
used it rather than just Worksheets("MAIN") and Worksheets(Ndx).

Thanks,

Doug

"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?