Hide/Unhide Worksheets in Excel 365

[Robert_Conklin]

I have the following sub that unhides all sheets in a workbook. I would like to use the button I assigned to the ribbon to hide all of the sheets when I click it again.

Sub Unhide_Multiple_Sheets()
Dim ws As Worksheet

    'The For-Next is a loop that loops through
    'each sheet in the active workbook
    
    For Each ws In ActiveWorkbook.Worksheets
        'Set the visible property of the sheet (ws)
        'to visible (xlSheetVisible)
        
        ws.Visible = xlSheetVisible

    Next ws

End Sub

I tried copying the code, but changing the ws.Visible setting to xlSheetHidden, but the button does nothing when the sheets are visible. Any help would be awesome!

 

[Michael M]

You can change to this code ....BUT, you cannot hide ALL sheets. One must be visible at all times or it will error

Sub Unhide_Multiple_Sheets()
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
       If ws.name<> "Sheet1" then
             ws.Visible = Not ws.Visible
       end if
    Next ws
End Sub

 

[Robert_Conklin]

That worked beautifully! I changed Sheet 1 to my Table of Contents sheet. Thank you very much for your assistance!!

 

[Michael M]

Glad to help...