Macro to hide all sheets except one

[y2k]

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

Thanx!!

[dk]

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

[Colo]

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

[sen_edp]

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 ]

[Ivan F Moala]

On 2002-05-14 01:35, sen_edp wrote:

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

[sen_edp]

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 ?

[y2k]

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

[sen_edp]

Thanks Ivan

[Ivan F Moala]

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.

[Ivan F Moala]

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