VBA list all worksheets

G

glonka

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
 
S

sp00nix

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"
 
F

Frank Kabel

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