List of worksheet names
 

Any way to put together a list of the names of all worksheets in a workbook
in an excel worksheet?

List of worksheet names
 

The free Excel add-in "XL Extras" will do that. It creates a table of contents
of sheet names with each name hyperlinked to the sheet.
The program does other nice stuff including sorting sheets and changing font case.
Download from... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA


"tor"
wrote in message
Any way to put together a list of the names of all worksheets in a workbook
in an excel worksheet?

List of worksheet names
 

The sub below will do what you want
As codes the list starts in E4

Sub mysheets()
myrow = 4
mycolumn = 5
For j = 1 To ActiveWorkbook.Worksheets.Count
Cells(myrow, mycolumn) = Worksheets(j).Name
myrow = myrow + 1
Next
End Sub

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tor" wrote in message
...
Any way to put together a list of the names of all worksheets in a
workbook
in an excel worksheet?

List of worksheet names
 

One method is a macro.

Insert a new sheet then run the macro.

Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP



On Tue, 20 Mar 2007 12:12:49 -0700, tor wrote:

Any way to put together a list of the names of all worksheets in a workbook
in an excel worksheet?

List of worksheet names
 

Paste this into a vb module and run. It will put all the worksheet names into
Col A of the active worksheet.

Sub namesheets()
x = 1
Dim sht As Worksheet
For Each sht In Sheets
ActiveSheet.Cells(x, 1) = sht.Name
x = x + 1
Next sht
End Sub

Mike

"tor" wrote:

Any way to put together a list of the names of all worksheets in a workbook
in an excel worksheet?