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!!

 

[y2k]

Works a treat... thank you all very much!!!

 

[poleary2000]

How do you get the sheets to unhide if the user selects Cancel when closing?

 

[DENWARD]

This is an oldie but a goodie ! Thanks

 

[Dokat]

I tried the code but didn't work. I changed sheet name to Main Menu and copy paste the code still no luck. Can anyone help me?

 

[David A]

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

Hi Dan, I tried your code but I'm getting this error message:
Run-time error 1004:
Unable to set the Visible property of the Worksheet class

Note i did change the sheet name accordingly

 

[David A]

I figured it out I need to Unhide my target sheet first, otherwise, everything will be hidden which is not an acceptable task. Thanks!