VBA list all worksheets
 

CAn anyone help me use this code from MSDN
I'm trying to easily generate a list of worksheets in a workbook.



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


Thanks
Bra

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

VBA list all worksheets
 

Code:
--------------------

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

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

--------------------



make sure you name a cell "rangeSheets" in your workbook.

Or you could simply replace "rangeSheets" above with your desired
range, ie "A1"


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

VBA list all worksheets
 

Hi
and what is your exact problem?. You have to define the
name 'rangesheets' first before running this macro

-----Original Message-----
CAn anyone help me use this code from MSDN
I'm trying to easily generate a list of worksheets in a
workbook.



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


Thanks
Brad


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

.