Hi Brad

That code was pretty messy and should definitely err, is it really from
MSDN? Try

Sub ListSheets()
Dim sh As Excel.Worksheet
For Each sh In ThisWorkbook.Worksheets
Sheets(1).Cells(sh.Index, 1).Value = sh.Name
Next sh
End Sub

(Note that it's worksheets, which you wanted, chart sheets will not get
listed.

HTH. Best wishes Harald

"glonka " skrev i melding
...
Hi --

I have some workbooks that have 100+ worksheets in them.
I'd like to have a list of the worksheets printed in a worksheet (or
any list of cells).

I found this code on the MSDN:

Private Sub ListSheets()
Dim sh As Excel.Worksheet
Dim rng As Excel.Range
Dim i As Integer

rng = ThisApplication.Range("rangeSheets")
For Each sh In ThisWorkbook.Sheets
rng.Offset(i, 0).Value = sh.Name
i = i + 1
Next sh
End Sub

I created a range of cells called rangeSheets.
This code does not work as is. It must need some
kind of modification that's not obvious.

I have two problems.....
1) I had the remove the word private for it to be visible in the
macros list (OK after that I think)

2) It creates a run time error at this line
rng = ThisApplication.Range("rangeSheets")
I've tried:
set rng = ThisApplication.Range("rangeSheets")
and
rng = ThisApplication.Range("A1")

nothing changes.

Any suggestion how to get a list of worksheets or how to use this
code?

Thanks
Brad


---
Message posted from http://www.ExcelForum.com/