List of Worksheet Name in a Workbook

  • Thread starter Thread starter Agnes
  • Start date Start date
A

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!
 
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
 
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
 
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


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



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

Our pleasure` Kitty !
Thanks for the feedback
 
Back
Top