Hide Worksheet except

[garypea123]

Hi,

I have a work book with around 20 tabs (work sheets)


At the beginning of my script I unhide them with

Application.ScreenUpdating = False

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Visible = xlSheetVisible

Next ws


But at the end of the script I would like to hide them all expect 3 or 4 (lets call them "Gary", "John","Jen","Karen". How do I hide all work books except the 4 I have listed please?

Thanks,
Gary

 

[Fluff]

How about

Sub garypea()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Select Case LCase(Ws.name)
         Case "gary", "john", "jen", "karen"
         Case Else
            Ws.Visible = xlSheetHidden
      End Select
   Next Ws
End Sub

 

[dmt32]

Hi,
untested but maybe

Sub hidesheets()
    Dim ws          As Worksheet
    Dim arr         As Variant
    Dim KeepVisible As Boolean
    
    arr = Array("Gary", "John", "Jen", "Karen")
    For Each ws In ActiveWorkbook.Worksheets
        KeepVisible = Not IsError(Application.Match(ws.Name, arr, 0))
        ws.Visible = IIf(KeepVisible, xlSheetVisible, xlSheetVeryHidden)
    Next ws
End Sub

Do be mindful that at least one sheet must remain visible otherwise code will error if none of the tab names in the list exist

Dave

 

[garypea123]

It does not work.

Maybe its the Lcase? The worksheet in this one example are called "Macro Sheet" and "Data Sheet"

It seems to want to hide all worksheets, but then errors when it gets to the last one and cannot hide anymore

Sub AAAA_Hide()
Dim Ws As Worksheet

For Each Ws In Worksheets
Select Case LCase(Ws.Name)
Case "Macro Sheet", "Data Sheet"
Case Else
Ws.Visible = xlSheetHidden
End Select
Next Ws
End Sub

Thanks,
Gary

 

[Fluff]

Change the sheet names in the case statement to lower case only

 

[garypea123]

Hi,
untested but maybe

Sub hidesheets()
    Dim ws          As Worksheet
    Dim arr         As Variant
    Dim KeepVisible As Boolean
   
    arr = Array("Gary", "John", "Jen", "Karen")
    For Each ws In ActiveWorkbook.Worksheets
        KeepVisible = Not IsError(Application.Match(ws.Name, arr, 0))
        ws.Visible = IIf(KeepVisible, xlSheetVisible, xlSheetVeryHidden)
    Next ws
End Sub

Do be mindful that at least one sheet must remain visible otherwise code will error if none of the tab names in the list exist

Dave

This does work, but why is it when I have ran the macro and then right click on tabs that Unhide is greyed out?

 

[Fluff]

Because that code makes the sheets very hidden, rather than just hidden.

 

[dmt32]

This does work, but why is it when I have ran the macro and then right click on tabs that Unhide is greyed out?

@Fluff kindly answered the question but if you don't want this then change part of code shown in BOLD to xlSheetHidden

ws.Visible = IIf(KeepVisible, xlSheetVisible, xlSheetVeryHidden)

Dave

 

[garypea123]

Fantastic - DMT32 & Fluff.

Both Scripts work like a Dream - And I learned about the very hidden and hidden

Thank you both ever so much

 

[Fluff]

Glad we could help & thanks for the feedback.