Results 1 to 6 of 6

Thread: hide selected sheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2018
    Posts
    308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default hide selected sheets

    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?

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: hide selected sheets

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Jun 2018
    Posts
    308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: hide selected sheets

    Nope, that hid all the sheets.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,737
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: hide selected sheets

    Quote Originally Posted by wmtsub View Post
    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's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,737
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: hide selected sheets

    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: hide selected sheets

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •