Quick summary of Sheet Names

  • Thread starter Thread starter Jim May
  • Start date Start date
J

Jim May

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
 
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)?
 
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")
 
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
 
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).
Click to expand...

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().
 
Back
Top