Macro to hide all sheets except one

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Macro to hide all sheets except one

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,933
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  4. #4
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 ?



    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Thanks Ivan



    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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
  •