Retrieve hidden sheets name dynamically

[Firasath]

Hi, can we retrieve all the hidden sheets names dynamically in one column either by formula or using VBA?

 

[Georgiboy]

The below will list them in column A:

Sub test()
  Dim ws As Worksheet, wsVar() As Variant, x As Long
  
  For Each ws In Sheets
    If Not ws.Visible Then
      ReDim Preserve wsVar(x): wsVar(x) = ws.Name: x = x + 1
    End If
  Next ws
  Range("A1").Resize(x) = Application.Transpose(wsVar)
End Sub

How do you mean "dynamically", does this mean you want the list to change when sheets are hidden/ made visible?

 

[Firasath]

The below will list them in column A:

Sub test()
  Dim ws As Worksheet, wsVar() As Variant, x As Long
 
  For Each ws In Sheets
    If Not ws.Visible Then
      ReDim Preserve wsVar(x): wsVar(x) = ws.Name: x = x + 1
    End If
  Next ws
  Range("A1").Resize(x) = Application.Transpose(wsVar)
End Sub

How do you mean "dynamically", does this mean you want the list to change when sheets are hidden/ made visible?

Thanks for your prompt reply. Yes, as you mentioned, the list should get changed when the visible sheet change to hidden or vice versa.

 

[Georgiboy]

If you are hiding/ unhiding the sheets manually then you can try the below in the 'ThisWorkbook' module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim ws As Worksheet, wsVar() As Variant, x As Long
  
  If Sh.Name <> "Sheet1" Then Exit Sub
  
  For Each ws In Sheets
    If Not ws.Visible Then
      ReDim Preserve wsVar(x): wsVar(x) = ws.Name: x = x + 1
    End If
  Next ws
  With Sheet1.Range("A1")
    .Resize(Sheets.Count).ClearContents
    If x > 0 Then .Resize(x) = Application.Transpose(wsVar)
  End With
End Sub

Where "Sheet1" in the code refers to the sheet where the sheet names will be listed.

 

[Alex Blakenburg]

@Georgiboy shouldn’t this be before your Redim statement.
x = x + 1
On 2nd thoughts I suspect it works it’s just not intuitive (to me)

 

[Georgiboy]

@Georgiboy shouldn’t this be before your Redim statement.
x = x + 1

Not necessarily, as long as x is defined as long the default value of x will be zero, as the array can start at zero, there is no need to increment x before use.

 

[Firasath]

If you are hiding/ unhiding the sheets manually then you can try the below in the 'ThisWorkbook' module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim ws As Worksheet, wsVar() As Variant, x As Long
 
  If Sh.Name <> "Sheet1" Then Exit Sub
 
  For Each ws In Sheets
    If Not ws.Visible Then
      ReDim Preserve wsVar(x): wsVar(x) = ws.Name: x = x + 1
    End If
  Next ws
  With Sheet1.Range("A1")
    .Resize(Sheets.Count).ClearContents
    If x > 0 Then .Resize(x) = Application.Transpose(wsVar)
  End With
End Sub

Where "Sheet1" in the code refers to the sheet where the sheet names will be listed.

Thanks a lot, it worked for me.