Please simply this macro - hide all sheets except specific set

[LenPL]

Hi,

The following macro works, however I am trying to clean up the code

Sub ShowSet1()
Dim xWs As Worksheet
Dim xName As String
xName = "Sheet5"
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> xName Then
xWs.Visible = xlSheetHidden
End If
Sheets("Sheet1").Visible = True
Sheets("Sheet123").Visible = True
Sheets("Sheet23").Visible = True
Next
End Sub

Thank you in advance,

Len

 

[Trevor G]

Try this

Sub Hide1A()
sHide = Array("Sheet1", "Sheet5", "Sheet23", "Sheet123")
For Each sName In sHide
Worksheets(sName).Visible = Visible
Next sName
End Sub

 

[LenPL]

Thanks for your Reply Trevor.

However this seems to hide the select sheets, rather than show.

 

[Michael M]

Maybe this

Sub ShowSet1()
Dim xWs As Worksheet, xName As String
xName = "Sheet5"
For Each xWs In Worksheets
If xWs.Name <> xName AND xWs.Name <> "Sheet1" AND xWs.Name <> "Sheet123" AND xWs.Name <> "Sheet23" Then
xWs.Visible = xlSheetHidden
End If
Next ws
End Sub

 

[dmt32]

another way maybe

Sub HideSheets()
    Dim NotHide As Variant, m As Variant
    Dim ws As Worksheet

    NotHide = Array("Sheet1", "Sheet5", "Sheet23", "Sheet123")

    For Each ws In Worksheets
        m = Application.Match(ws.Name, NotHide, False)
        ws.Visible = Not IsError(m)
    Next ws

End Sub

Dave

 

[LenPL]

Maybe this

Sub ShowSet1()
Dim xWs As Worksheet, xName As String
xName = "Sheet5"
For Each xWs In Worksheets
If xWs.Name <> xName AND xWs.Name <> "Sheet1" AND xWs.Name <> "Sheet123" AND xWs.Name <> "Sheet23" Then
xWs.Visible = xlSheetHidden
End If
Next ws
End Sub

Thanks Michael, unfortunatley a "Complie error: Invalid Next control variable" reference pops up at the red text.

 

[cooper645]

[COLOR=#222222][FONT=Times New Roman]Sub ShowSet1()
Dim xWs As Worksheet, xName As String
xName = "Sheet5"
For Each xWs In Worksheets
If xWs.Name <> xName And xWs.Name <> "Sheet1" And xWs.Name <> "Sheet123" And xWs.Name <> "Sheet23" Then
xWs.Visible = 0
End If
Next xWs
End Sub
[/FONT][/COLOR]

 

[LenPL]

another way maybe

Sub HideSheets()
    Dim NotHide As Variant, m As Variant
    Dim ws As Worksheet

    NotHide = Array("Sheet1", "Sheet5", "Sheet23", "Sheet123")

    For Each ws In Worksheets
        m = Application.Match(ws.Name, NotHide, False)
        ws.Visible = Not IsError(m)
    Next ws

End Sub

Dave

Perfect!! Smooth and professional running, just what I was looking for.

Thanks Dave, this thread is now SOLVED.

 

[dmt32]

Perfect!! Smooth and professional running, just what I was looking for.

Thanks Dave, this thread is now SOLVED.

Many thanks for feedback it is very much appreciated & glad solution worked for you.

Just an aside, you could if you have other permutations of worksheets you need to keep visible make the code a common procedure and call if by passing the array as an argument.

Sub HideSheets(ByVal NotHide As Variant)
    Dim m As Variant
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        m = Application.Match(ws.Name, NotHide, False)
        ws.Visible = Not IsError(m)
    Next ws
    
End Sub

and call it like this:

Sub ab()
    Dim wsArray As Variant
    wsArray = Array("Sheet1", "Sheet5", "Sheet23", "Sheet123")
    
    HideSheets NotHide:=wsArray
End Sub

Dave

 

[LenPL]

Thanks Dave,

I have used your second recommendation, as I had a number of specific sets.