Get a List of Worksheets
 

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/

Get a List of Worksheets
 

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/

Get a List of Worksheets
 

thanks so much -- that works just fine!!!


Here is the offending URL
http://tinyurl.com/6nll

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

Get a List of Worksheets
 

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

"glonka " skrev i melding
...
thanks so much -- that works just fine!!!


Here is the offending URL
http://tinyurl.com/6nlly


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

Get a List of Worksheets
 

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" wrote in message
...
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/

Get a List of Worksheets
 

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

--
Regards,
Tom Ogilvy


"Harald Staff" wrote in message
...
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

"glonka " skrev i melding
...
thanks so much -- that works just fine!!!


Here is the offending URL
http://tinyurl.com/6nlly


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

Get a List of Worksheets
 

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

"Tom Ogilvy" skrev i melding
...
I believe it is VB.NET code. Not VBA.