How to list all sheets

[yosi_lb]

Hi there

how can i retrive all the sheets names (or Caption) available in a workbook
and how can i find if a sheet exsist on the workbook

thak all it's a great newsgroup

 

[Guest]

Enter and run this small macro:

Sub sheetlist()
Dim s As String
s = ""
For Each w In Worksheets
s = s & Chr(10) & w.Name
Next
MsgBox (s)
End Sub

 

[Michael Bednarek]

how can i retrive all the sheets names (or Caption) available in a workbook
and how can i find if a sheet exsist on the workbook

Sub ListAllSheets(wbkBook As Workbook)

Dim shtSheet As Worksheet

For Each shtSheet In wbkBook.Sheets
Debug.Print shtSheet.Name
Next shtSheet

End Sub
'========
Function SheetExists(wbkBook As Excel.Workbook, strSheetName As String) As Boolean

Dim shtSheet As Worksheet

SheetExists = True
For Each shtSheet In wbkBook.Sheets
' This is case sensitive; making it not is left as an exercise for the reader.
If shtSheet.Name = strSheetName Then Exit Function
Next shtSheet
SheetExists = False
End Function

 

[Gord Dibben]

Try this version.

Will give a list of all sheet names starting at A1 on the active sheet.

Insert a new sheet and have that sheet active when you run the macro.

Will list all sheets including hidden sheets.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Private Subs are not listed in Tools>Macro>Macros because they
are.........Private and not for viewing by snoops.

It would still work, you would just have to type the name into the dialog box
then run it.

Or assign to a button or shortcut key combo.

My error in leaving the sub as Private when posting.


Gord

 

[Dave Peterson]

Tools|Macro|macros...
Type it into the Macro Name box.

 

[Guest]

It's me again!

I figured it out. THANKS AGAIN!

 

[Harlan Grove]

Gord Dibben wrote...

Private Subs are not listed in Tools>Macro>Macros because they
are.........Private and not for viewing by snoops.

Since all procedure names are visible when viewing XLS files with a hex
editor, Private only prevents viewing by the village idiots, not
dedicated and knowledgeable snoops.

That said, better to make the procedure a function which could return
the list of worksheet names either as an array or as a long text
string. Much, much more flexible.


Function wslst(Optional rettxt As Boolean = False) As Variant
Dim rv As Variant, k As Long, wsc As Sheets

If TypeOf Application.Caller Is Range Then
Set wsc = Application.Caller.Parent.Parent.Sheets
Else
Set wsc = ActiveWorkbook.Sheets
End If

If Not rettxt Then ReDim rv(1 To wsc.Count, 1 To 1)

For k = 1 To wsc.Count
If rettxt Then
rv = rv & vbLf & wsc(k).Name
Else
rv(k, 1) = wsc(k).Name
End If
Next k

If rettxt Then
wslst = Mid(rv, 2)
Else
wslst = rv
End If

End Function

 

[Harlan Grove]

Michael Bednarek wrote...

....
Function SheetExists(wbkBook As Excel.Workbook, strSheetName As String) As Boolean
Dim shtSheet As Worksheet

SheetExists = True
For Each shtSheet In wbkBook.Sheets
' This is case sensitive; making it not is left as an exercise for the reader.
If shtSheet.Name = strSheetName Then Exit Function
Next shtSheet
SheetExists = False
End Function

Wouldn't

=ISNUMBER(ROWS(INDIRECT("'"&WorksheetNameHere&"'!A1")))

accomplish the same thing without VBA?

 

[Dave Peterson]

I would put this kind of macro in a General module--not behind a worksheet and
not behind ThisWorkbook.

 

[Michael Bednarek]

Michael Bednarek wrote...

Wouldn't

=ISNUMBER(ROWS(INDIRECT("'"&WorksheetNameHere&"'!A1")))

accomplish the same thing without VBA?

Indeed it would. Thanks for that.