Unhide All Sheets

What it does

Excel's right-click > Unhide menu shows hidden sheets one at a time. To unhide twelve sheets, you do twelve right-clicks. Worse, "very hidden" sheets (set via VBA) don't show up in the right-click menu at all. The macro reveals every hidden and very-hidden sheet in the active workbook in a single pass.

Real-world example

Picture a treasury manager at a multi-entity holding company who inherits the consolidation workbook from a departing colleague. The colleague hid 7 sheets she considered "internal working tabs" and very-hid 3 more she thought "shouldn't be touched." The new manager doesn't know which tabs exist; she just knows the workbook has more than the 12 visible ones because the SUMIF formulas reference cells on tab names she's never seen.

Her current method: right-click any tab, click Unhide, see a list of 7 hidden sheets, pick one, repeat 7 times. The 3 very-hidden sheets don't appear; she discovers them later by opening the VBA editor (Alt+F11) and seeing them in the Project pane.

With this macro she opens the workbook, runs the tool, and all 10 hidden/very-hidden sheets become visible in 1 second. The popup confirms count. She walks the workbook structure, decides which to keep visible, and re-hides the ones that should stay hidden.

On a 22-tab workbook with 10 hidden tabs of various flavors, expect about 90 seconds saved versus the right-click method, plus the discovery of the very-hidden tabs that wouldn't show up in the menu.

How to use

First time? See How to run our tools for the 30-second setup.

1. Open the workbook. The macro operates on the active workbook.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick UnhideAllSheets, click Run.
3. The macro makes every hidden and very-hidden sheet visible. The popup reports how many sheets were unhidden.

FAQ

What's the difference between hidden and very-hidden?

A regular hidden sheet (xlSheetHidden) shows up in the right-click > Unhide menu. A very-hidden sheet (xlSheetVeryHidden) does not; it can only be unhidden via VBA (or by macros like this one). The macro handles both states.

Will it leave the sheets unhidden permanently?

Yes, until you re-hide them. After running the macro, every previously-hidden sheet is set to xlSheetVisible. To re-hide selectively, right-click the tabs you want hidden and choose Hide. To re-hide via VBA (very-hide), set Sheets("name").Visible = xlSheetVeryHidden.

Does it activate any of the unhidden sheets?

No. The active sheet stays the active sheet. The macro only changes the Visible property of the others.

What if the workbook is protected at the workbook level (not sheet level)?

Workbook-level protection prevents structural changes including sheet visibility. The macro will error. Unprotect the workbook first via Review > Protect Workbook (toggle off), then run the macro.

Can I undo it?

Ctrl/Cmd + Z does NOT reliably undo Visible property changes across all Excel versions. To re-hide sheets, do it manually via right-click > Hide, or write a 3-line "HideAllExceptActive" companion macro. The macro runs entirely offline. Your workbook never leaves your machine.