List of Worksheet Name in a Workbook

[Agnes]

My workbook contains about 30 sheets and each sheet has a
unique and meaningful name. I can see a list of sheets
thru File-Properties-Contents, but I can't copy and paste
that information. Is there any way I can get that info in
an editable format?

Your help is appreciated!

 

[Max]

Try the sub below:

Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines below
into the whitespace on the right

-------begin vba-----
Sub SheetNames()
'Peo Sjoblom in .worksheet.functions Jul '02
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
-------endvba------

Press Alt+Q to get back to Excel

In a *new* sheet, press Alt+F8
Select "SheetNames" > Run

The sheetnames will be listed in A1 down, in this sequence:

1st sheet (leftmost) will be listed in A1,
2nd sheet in A2, and so on

Hidden sheets will also be listed
and will appear after the last (rightmost) sheet

 

[Juan Sanchez]

Agnes

You can acomplish this with a MACRO, here's the code you
can use....

In a module in your personal macro book, or the active
book put this code...

Sub Index()

Dim WS As Worksheet

For Each WS In Worksheets
Cells(WS.Index, 1).Value = WS.Name
Next WS

End Sub

Run the macro from a fresh page that you can probably name
INDEX. It will list de names of the pages from A1 to
An..what ever n = to. and it will overwrite anything with
in column A, so make sure you use a fresh page...

Post back if you have trouble creating the macro...

Cheers
Juan S

 

[Agnes]

This works! Thanks a lot!

-----Original Message-----
Agnes

You can acomplish this with a MACRO, here's the code you
can use....

In a module in your personal macro book, or the active
book put this code...

Sub Index()

Dim WS As Worksheet

For Each WS In Worksheets
Cells(WS.Index, 1).Value = WS.Name
Next WS

End Sub

Run the macro from a fresh page that you can probably name
INDEX. It will list de names of the pages from A1 to
An..what ever n = to. and it will overwrite anything with
in column A, so make sure you use a fresh page...

Post back if you have trouble creating the macro...

Cheers
Juan S


.

 

[Agnes]

This works too! Thanks a lot!

-----Original Message-----
Try the sub below:

Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines below
into the whitespace on the right

-------begin vba-----
Sub SheetNames()
'Peo Sjoblom in .worksheet.functions Jul '02
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
-------endvba------

Press Alt+Q to get back to Excel

In a *new* sheet, press Alt+F8
Select "SheetNames" > Run

The sheetnames will be listed in A1 down, in this sequence:

1st sheet (leftmost) will be listed in A1,
2nd sheet in A2, and so on

Hidden sheets will also be listed
and will appear after the last (rightmost) sheet

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----



.

 

[Max]

Pleasure' Agnes !
Thanks for the feedback

 

[Juan Sanchez]

Thanks Agnes, appreciate your time to post back with
thanks!!!

 

[Kitty]

You folks who are answering this question, thanks SO much!
I also needed to get this done. Works great!
Kitty

 

[Max]

You folks who are answering this question, thanks SO much!
I also needed to get this done. Works great!
Kitty

Our pleasure` Kitty !
Thanks for the feedback