Adding to a "list sheet names" macro ...

[StargateFanNotAtHome]

Thanks, as always, to the group for all the great help! We wouldn't
be as effective in our jobs without the help received here! <g>

I have a few "list sheet names" macros but am missing a couple of
features which I'm hoping are easy to add.

Here is the macro that I'm working with now:

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


1) I had one at one point that created a list of sheet names but
without including the list itself, the "ListOfSheetNames" one, in this
case. But after juggling and playing with a few, can't find the code
that did that. What needs to be changed above, pls, so that we'd get
a list of the original sheets only.

i.e., if I have sheets with the names of, say:

Addresses
Bill Payments
Financial Institutions

the list would just have those 3 sheet names and _not_ this:

ListOfSheetNames
Addresses
Bill Payments
Financial Institutions

2) Secondly, would there be a way to add numbering to the list itself
so that we'd get this type of thing?:

[1] Addresses
[2] Bill Payments
[3] Financial Institutions

The current workbook has many sheets and it would help when dealing
with them to have a # before them in the list.

Thanks! D

 

[JLGWhiz]

For Each Sheet In ActiveWorkbook.Sheets
If Not Sheet.Name = "ListOfSheetNames" Then
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet

 

[Per Jessen]

HI

With numbers in column A and Sheet names in column B try this:

Sub SHEET_NAMES_list_all()
'list of sheet names starting at B1
Dim Rng As Range
Dim Sheet As Worksheet
Dim i As Long

Worksheets.Add(Before:=Worksheets(1)).Name = "ListOfSheetNames"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "ListOfSheetNames" Then
Rng.Offset(i, 1).Value = Sheet.Name
Rng.Offset(i, 0).Value = i + 1
i = i + 1
End If
Next
End Sub

Regards,
Per