Names of Sheets and Charts in Workbook

[Guest]

Would like to find the Nanes of all the Worksheets and Chart Sheets within Workbook

Thank

 

[Chip Pearson]

Try something like the following:

Dim SH As Object
For Each SH In ThisWorkbook.Sheets
Debug.Print SH.Name
Next SH

This will also list Excel4 macro sheet and Excel5 module sheets,
but it is very unlikely that you would have these in any workbook
created in Excel 97 or later.

 

[Jake Marx]

Hi Sonny,

You can loop through the Sheets collection to do this:

Sub test()
Dim sht As Object

For Each sht In ActiveWorkbook.Sheets
Debug.Print sht.Name & " (Type: " & IIf(sht.Type = _
xlWorksheet, "Worksheet", "Chart") & ")"
Next sht
End Sub

There are a few other types of sheets, so the IIf statement will only work
if you are only concerned with Worksheets and Chart sheets.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Frank Kabel]

Hi
have a look at
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[Guest]

Thanks worked fine