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(Befo=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! :oD

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




wrote in message
...
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(Befo=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! :oD

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(Befo=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


skrev i meddelelsen
...
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(Befo=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! :oD