Macro to Hide and Unhide Worksheets in Excel

These VBA macros hide every sheet except the one you’re on, unhide all hidden sheets at once, and hide the sheets you’ve selected.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Hide all sheets except the active one

Sub HideAllExceptActive()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
    Next ws
End Sub

Useful before sharing a workbook where only one sheet matters, such as a dashboard.

Unhide all sheets

Excel’s own Unhide dialog only lets you pick one sheet at a time in older versions. This unhides them all:

Sub UnhideAllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

It also unhides very hidden sheets. See very hidden sheets for what those are.

Hide the selected sheets

Select several sheet tabs with Ctrl, then run:

Sub HideSelectedSheets()
    ActiveWindow.SelectedSheets.Visible = False
End Sub

Things to know

  • At least one sheet must stay visible. Hiding them all stops the macro with an error.
  • Hidden sheets still work normally: formulas that refer to them keep calculating.
  • Hiding isn’t security. Anyone can unhide a normal hidden sheet with right-click → Unhide.

Related macros