hide selected sheets

[wmtsub]

I would like to loop thru the work book and hide all sheets except a given list. Below are two macros I wrote, neither works. Can anyone tweak one or the other?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Macro1()
For Each sh In ActiveWorkbook.Worksheets
Select Case sh.Name
Case Is = "Summary", "Sheet1", "Assa Abloy Entr", "Cornell Storefr", "Dh PAce Company", "Stanley Access", "Thyssenkrupp El", "Won door Corp"
Case Else
MsgBox sh.Name
ActiveWindow.SelectedSheets.Visible = False
End Select
Next sh
End Sub


Sub macro2()
For Each ws In Sheets:
If ws = "Summary" Then ws.Visible = True
If ws = "Sheet1" Then ws.Visible = True
If ws = "Assa Abloy Entr" Then ws.Visible = True
If ws = "Cornell Storefr" Then ws.Visible = True
If ws = "Dh Pace Company" Then ws.Visible = True
If ws = "Stanley Access" Then ws.Visible = True
If ws = "Thysssenkrupp El" Then ws.Visible = True
If ws = "won door" Then ws.Visible = True
ws.Visible = False
Next
End Sub
[/FONT]

 

[Joe4]

Try this:

Sub Macro1()
    Dim sh As Worksheet
    For Each sh In Worksheets
        Select Case sh.Name
            Case Is = "Summary", "Sheet1", "Assa Abloy Entr", "Cornell Storefr", "Dh PAce Company", "Stanley Access", "Thyssenkrupp El", "Won door Corp"
            Case Else
                sh.Visible = False
        End Select
    Next sh
End Sub

 

[wmtsub]

Nope, that hid all the sheets.

 

[Rick Rothstein]

Nope, that hid all the sheets.

Is the list of sheet names you gave us all of the sheets in your workbook by any chance?

 

[Rick Rothstein]

If you know the names of the sheets you want to hide, you could put them into an Array function call, embed that in Sheets object call set to make those sheets hidden. Assuming you wanted to hide these four sheets...

Assa Abloy Entr
Dh PAce Company
Stanley AccessWon door Corp

then this single line of code would do it...

Sheets(Array("Assa Abloy Entr", "Dh PAce Company", "Stanley Access", "Won door Corp")).Visible=False

 

[Joe4]

Nope, that hid all the sheets.

Most likely that means that none of your sheet names exactly match the names in that list. You could have extra spaces or other characters in the names.