Get a List of Worksheets

G

glonka

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
 
H

Harald Staff

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
 
H

Harald Staff

Thanks. Incredible. That code can't have been tested ever. It should at
least read
Set rng = Range("rangeSheets")
-ranges must be assigned with "Set". And "ThisApplication" is, in this
context, pure nonsense.

Best wishes Harald
 
T

Tom Ogilvy

If you adjust one line, it works oK (and have a defined name rangesheets).

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

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

--
Regards,
Tom Ogilvy

Harald Staff said:
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
Click to expand...
 
T

Tom Ogilvy

I believe it is VB.NET code. Not VBA.

--
Regards,
Tom Ogilvy


Harald Staff said:
Thanks. Incredible. That code can't have been tested ever. It should at
least read
Set rng = Range("rangeSheets")
-ranges must be assigned with "Set". And "ThisApplication" is, in this
context, pure nonsense.

Best wishes Harald
Click to expand...
 
H

Harald Staff

Ah. Yes, "Visual Studio Tools for Office 2003". There's Visual Basic and
there's Visual Basic, and I spent much of this summer studying the
differences <blush>. Apologies, and thanks.

Best wishes Harald
 
Top