How do I list out names of Worksheets in Excel file?

[Cosmos75]

I am trying to list out the names of the worksheets that I have in an excel file. Say my excel file has 15 worksheets, with names A1, A2, A3, ... , A10. I want to create another worksheet (in the same excel file) that has a list of those 10 worksheet names. Is there any way to do this?

 

[Mudface]

Insert a worksheet called summary, and, in the VB editor insert a module. Copy and paste this code into the module. It will put the names of the sheets and charts you have in the workbook in column A on the 'Summary' sheet.


Public Sub CountingSheets()

Dim x As Integer
Dim ws As Object

Sheets("Summary").Columns("A:A").Delete Shift:=xlToLeft

For Each ws In Sheets
x = x + 1
Sheets("Summary").Range("A" & x).Value = ws.Name
Next ws

End Sub

Hope this is OK.
This message was edited by Mudface on 2002-03-01 11:26

 

[Cosmos75]

Thanks! It works great!