Excel's ThisWorkbook object lets you expose and change a worksheet's visibility.
Private Sub ReportVisibility()
'List very hidden sheets in Immediate window
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVeryHidden Then
Debug.Print ws.Name & " is very hidden"
ElseIf ws.Visible = xlSheetHidden Then
Debug.Print ws.Name & " is hidden"
Of course, you can modify this procedure to do most anything you want with the hidden sheets. Just add the appropriate code to the If statement. This particular sub doesn't explicitly identify visible sheets. Anything not listed should be visible. If you want to see a comprehensive list of all sheets, add a condition for visible—xlSheetVisible.