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]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top