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

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




<(E-Mail Removed)> wrote in message
news:e00dabfd-be9c-4075-9a10-(E-Mail Removed)...
> 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

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


<(E-Mail Removed)> skrev i meddelelsen
news:e00dabfd-be9c-4075-9a10-(E-Mail Removed)...
> 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