Display worksheet Visibility Status

[scotthannaford1973]

Hi all

I have a workbook with a lot of worksheets that get hidden and unhidden a lot and I'd like to add a table that, for each worksheet listed, there is a cell that shows that worksheet's visibility table.

Is there any code I can use that just shows that status and adds it into a cell? I don't want to list the status for every worksheet in the workbook, but just for those I list in the table. It could be one piece of code listing them all, or one piece of code per worksheet.

thanks!

WORKSHEET NAME	VISIBILITY STATUS
Worksheet2	Visible
Worksheet3	Hidden
Worksheet4	Hidden
Worksheet5	Visible

 

[Peter_SSs]

What worksheet is the table in?
What cell is the heading "WORKSHEET NAME" in on that sheet?

 

[scotthannaford1973]

What worksheet is the table in?
What cell is the heading "WORKSHEET NAME" in on that sheet?

Hi Peter

lets just say A1 and B1 - I can always update any cell refs in the code.

thanks

 

[Peter_SSs]

What about the first question?

 

[scotthannaford1973]

What about the first question?

sorry missed that - Sheet1

 

[Peter_SSs]

Thanks. Try this

Sub List_Visibility()
  Dim c As Range
  
  With Sheets("Sheet1")
    For Each c In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
      c.Offset(, 1).Value = IIf(Sheets(c.Value).Visible, "Visible", "Hidden")
    Next c
  End With
End Sub