hide sheet that not in array

Question

I made array with the name of sheets that I want to select. all the other sheets I want to hide. its need to by dynamic.

list say that my array is Sheets(Array("Sheets1", "sheet2")).Select and I have 4 sheets how I can hide all the others sheet that not in the array ?

VBasic2008 · Accepted Answer · 2022-05-15 07:34:01Z

3

Hide Sheets

The Sheets collection includes worksheets, charts, and whatnot.
Here is a similar solution for worksheets.

Option Explicit

Sub HideSheets()
    
    ' Do NOT hide:
    Dim Exceptions As Variant: Exceptions = Array("Sheet1", "Sheet2")
    
    Dim sh As Object
    
    For Each sh In ThisWorkbook.Sheets
        If IsError(Application.Match(sh.Name, Exceptions, 0)) Then
            On Error Resume Next ' when trying to hide last visible sheet
                If Not sh.Visible = xlSheetHidden Then
                    sh.Visible = xlSheetHidden
                End If
            On Error GoTo 0
        End If
    Next sh
    
End Sub

answered May 15, 2022 at 7:34

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

hide sheet that not in array

1 Answer 1

Hide Sheets

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Hide Sheets

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related