Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing worksheets from a list | Excel Discussion (Misc queries) | |||
List of worksheets | New Users to Excel | |||
Get a list of worksheets from a WB | Excel Discussion (Misc queries) | |||
how do i list the name off all my worksheets on a sheet | Excel Worksheet Functions | |||
VBA list all worksheets | Excel Programming |