Visible WorkSheets

[charles]

I need to know how many worksheets are set to ".visible"
in my workbook.

Can you tell me how to do that ,and, also how to list them
using VBA.


Thanks

CharlesW

 

[Ron de Bruin]

Hi Charles

Try this

Sub test()
'xlSheetVisible = -1
Dim sh As Worksheet
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = -1 Then
N = N + 1
' Cells(N, 1).Value = sh.Name
' you can list them on the activesheet like this
End If
Next
With ThisWorkbook
msgbox "There are " & N & " Visible worksheets of the " _
& .Worksheets.Count & " Worksheets"
End With
End Sub

 

[Bob Phillips]

Charles,

You can also change the line

If sh.Visible = -1 Then

to be

If sh.Visible = xlSheetVisible Then

which is a bit more descriptive, or even

If sh.Visible Then

 

[charles]

Ron / Bob


This code works perfectly.


Thanks


CharlesW