hide selected sheets

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
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]
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,430
Office Version
365
Platform
Windows
Try this:
Code:
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,923
Office Version
2010
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,430
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,316
Messages
5,467,882
Members
406,558
Latest member
MattJC7

This Week's Hot Topics

Top