Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Macro to hide all sheets except one

This is a discussion on Macro to hide all sheets except one within the Excel Questions forums, part of the Question Forums category; Is it possible to write a macro that will hide all sheets except one? If so, how do I go ...

  1. #1
    y2k
    y2k is offline
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133

    Default

    Is it possible to write a macro that will hide all sheets except one? If so, how do I go about it?

    Thanx!!

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default

    Hi,

    Say you wanted all sheets hidden except Sheet1 you can use this:-

    Sub HideAllSheetsBarOne()
    Dim sht As Object

    For Each sht In Sheets
    If sht.Name <> "Sheet1" Then
    sht.Visible = xlSheetHidden
    End If
    Next sht

    End Sub


    If you want the sheets hidden so they can't be unhidden using Format, Sheet, Unhide change the xlSheetHidden bit it xlSheetVeryHidden. That way the sheets in question can only be unhidden using VBA.

    HTH,
    Dan

  3. #3
    MrExcel MVP Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,436

    Default

    Hi,
    Please try this, and you will need to modify the sheet name.


    Sub test()
    Dim sh As Worksheet
    For Each sh In Worksheets
    If sh.Name <> "Sheet1" Then sh.Visible = xlSheetHidden
    Next
    End Sub

    Regards,

    Masaru Kaji aka Colo - Ex Microsoft MVP Since 2004 -2009

  4. #4
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    554

    Default

    On 2002-05-14 01:28, Colo wrote:
    Hi,
    Please try this, and you will need to modify the sheet name.


    Sub test()
    Dim sh As Worksheet
    For Each sh In Worksheets
    If sh.Name <> "Sheet1" Then sh.Visible = xlSheetHidden
    Next
    End Sub

    What if you want to show them again?

    _________________
    Best Regards,
    Andreas
    using Excel 2000 - Windows 2000 prof.

    [ This Message was edited by: sen_edp on 2002-05-14 01:36 ]

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    On 2002-05-14 01:35, sen_edp wrote:
    On 2002-05-14 01:28, Colo wrote:
    Hi,
    Please try this, and you will need to modify the sheet name.


    Sub test()
    Dim sh As Worksheet
    For Each sh In Worksheets
    If sh.Name <> "Sheet1" Then sh.Visible = xlSheetHidden
    Next
    End Sub

    What if you want to show them again?

    _________________
    Best Regards,
    Andreas
    using Excel 2000 - Windows 2000 prof.

    [ This Message was edited by: sen_edp on 2002-05-14 01:36 ]

    Sub test()
    Dim sh As Worksheet
    For Each sh In Worksheets
    sh.Visible = True
    Next
    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    554

    Default

    Hello Ivan,

    Sorry I was not clear , i mean if you
    use in the hide macro the xlveryhidden command, what you put in the unhide macro ?


    Best Regards
    Andreas

  7. #7
    y2k
    y2k is offline
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133

    Default

    Thank you both very much, they work a treat. One final question (at least I hope it's the final question!). How can I tell it to unhide all of the sheets except one? It's the same one each time, namely 2003.

    Thanks again, you've really been a great help

  8. #8
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    554

    Default


    Thanks Ivan


    Best Regards
    Andreas

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    On 2002-05-14 01:51, sen_edp wrote:
    Hello Ivan,

    Sorry I was not clear , i mean if you
    use in the hide macro the xlveryhidden command, what you put in the unhide macro ?
    Hi sen_edp Use the code I gave above...will
    work even if xlveryhidden is set.
    The const xlSheetveryhidden only hides it
    from the Window > Unhide view.


    Kind Regards,
    Ivan F Moala From the City of Sails

  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    On 2002-05-14 01:54, y2k wrote:
    Thank you both very much, they work a treat. One final question (at least I hope it's the final question!). How can I tell it to unhide all of the sheets except one? It's the same one each time, namely 2003.

    Thanks again, you've really been a great help

    Sub test1()
    Dim sh As Worksheet
    For Each sh In Worksheets
    If sh.Name <> "2003" Then sh.Visible = xlSheetVisible
    Next
    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

Page 1 of 2 12 LastLast

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
  •  


DMCA.com