Hide Sheets vba

user_8490765 0
Viewed 81

I need to be able to hide all sheets apart from the ones in the array. I have written the code for the array but am now stuck for the rest.

Sub ShowHideWorksheets()
arr = Array("Readme", "Compliance cert", "Cash Balances", "Occupancy Report", "ALPH", "BC", "Bish", 
"GC", "HS", "STB", "WOL", "GroupCo", "OpCos", "RCG_ALL")

For Each Value In arr


Next Value
End Sub

Any help will be appreciated.

vba excel
2 Answers

Hide Worksheets

Option Explicit

Sub hideWorksheets()
    
    Dim arr As Variant
    arr = Array("Readme", "Compliance cert", "Cash Balances", _
                "Occupancy Report", "ALPH", "BC", "Bish", "GC", "HS", "STB", _
                "WOL", "GroupCo", "OpCos", "RCG_ALL")
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        If IsError(Application.Match(ws.Name, arr, 0)) Then
            ws.Visible = xlSheetHidden
        End If
    Next ws

End Sub

Sub showAllWorksheets()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetHidden Then ws.Visible = xlSheetVisible
    Next ws

End Sub
user_98140690

This worked for me

Sub ShowHideWorksheets()
    
    arr = Array("Readme", "Compliance cert", "Cash Balances", "Occupancy Report", "ALPH", "BC", "Bish", "GC", "HS", "STB", "WOL", "GroupCo", "OpCos", "RCG_ALL")
        
    Dim sh As Worksheet, foundSheet As Boolean
    For Each sh In Worksheets
        For Each Value In arr
            If sh.Name = Value Then
                foundSheet = True
                Exit For
            End If
        Next Value
        If Not foundSheet Then
            sh.Visible = xlSheetHidden
        End If
        foundSheet = False
    Next sh
    
End Sub
user_66009400
Related