Unhide All Sheets: The One Macro Every Inherited Workbook Needs

TL;DR: You inherit a workbook with hidden sheets. Excel makes you unhide them one at a time — right-click, Unhide, pick one, repeat. This macro unhides all of them in under a second and tells you exactly how many it found. Paste it once, run it forever.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with hidden sheets
• That’s it. No configuration, no named sheets, no setup.

Limitations:

• Does not unhide “very hidden” sheets (xlSheetVeryHidden) — those are typically set by VBA code and are hidden for a reason (macros, data storage, UI state). The macro reports them separately so you know they exist.
• Works on ThisWorkbook — the workbook containing the macro. If you want to run it on an external file, paste the macro into that file or store it in your Personal Macro Workbook.

#The Macro

Option Explicit

Sub UnhideAllSheets()
    ' ── Unhide All Sheets ──────────────────────────────
    ' Makes every hidden sheet in the workbook visible.
    ' Reports how many were unhidden, how many were
    ' already visible, and how many are very-hidden
    ' (those are left alone — they're hidden by design).
    '
    ' No configuration. No input. Just run it.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim unhidden As Long
    Dim alreadyVisible As Long
    Dim veryHidden As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    unhidden = 0
    alreadyVisible = 0
    veryHidden = 0

    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Visible
            Case xlSheetVisible
                alreadyVisible = alreadyVisible + 1
            Case xlSheetHidden
                ws.Visible = xlSheetVisible
                unhidden = unhidden + 1
            Case xlSheetVeryHidden
                veryHidden = veryHidden + 1
        End Select
    Next ws

    ' ── Report results ─────────────────────────────────
    Dim msg As String
    msg = unhidden & " sheet(s) unhidden." & vbCrLf & _
          alreadyVisible & " sheet(s) were already visible."

    If veryHidden > 0 Then
        msg = msg & vbCrLf & vbCrLf & _
              veryHidden & " sheet(s) are very-hidden " & _
              "and were left alone."
    End If

    MsgBox msg, vbInformation, "Unhide All Sheets"

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Three states, not two

Excel sheets have three visibility states, not two. Most people think “visible” and “hidden,” but there’s a third: xlSheetVeryHidden. A very-hidden sheet cannot be unhidden through the Excel UI — you can’t even see it in the Unhide dialog. It can only be revealed via VBA.

Select Case ws.Visible
    Case xlSheetVisible      ' Normal visible tab
    Case xlSheetHidden       ' User-right-clicked → Hide
    Case xlSheetVeryHidden   ' Set by VBA code, invisible to UI
End Select

The macro unhides xlSheetHidden sheets but leaves xlSheetVeryHidden alone. Very-hidden sheets are typically set deliberately — a developer hid a settings sheet or a helper table that the user was never supposed to see. Unhiding them clutters the tab bar and might expose confusing internal data. The macro still reports the count so you know they exist, but it respects the original developer’s intent.

#Why a Select Case instead of an If/ElseIf

A Select Case on an enum value is cleaner than a chain of If/ElseIf statements, but there’s a subtler reason: it forces you to handle all three states explicitly. An If ws.Visible = xlSheetVisible Then ... Else ... pattern would collapse hidden and very-hidden into the same branch. The Select Case makes it obvious that very-hidden sheets are a deliberate third case, not an oversight.

You could write the same logic with:

If ws.Visible = xlSheetVisible Then
    alreadyVisible = alreadyVisible + 1
ElseIf ws.Visible = xlSheetHidden Then
    ws.Visible = xlSheetVisible
    unhidden = unhidden + 1
Else
    veryHidden = veryHidden + 1
End If

Both work. The Select Case reads more like a state machine — “depending on which state this sheet is in, do this.” For a macro this small, either is fine.

#No Application.Calculation toggle needed

Most macros on this blog toggle Application.Calculation = xlCalculationManual to avoid triggering a recalculation cascade on every cell write. This macro doesn’t write to any cells — it only changes the .Visible property of each sheet. The Calculation toggle is unnecessary overhead here, so it’s omitted.

Application.ScreenUpdating = False is still included. Without it, Excel redraws the tab bar after each sheet is unhidden, which makes the macro visibly flicker. For 7 sheets it’s annoying; for 30 it makes Excel look like it’s crashing.

#The message box is the audit trail

A silent macro that unhides sheets is dangerous. You run it, nothing changes visibly (all the sheets were hidden, so the tab bar looks the same at a glance), and you don’t know if it worked. The message box tells you exactly what happened:

3 sheet(s) unhidden.
12 sheet(s) were already visible.
2 sheet(s) are very-hidden and were left alone.

You can immediately verify: if you expected 5 hidden sheets but the macro found 3, something’s off. Maybe two sheets you thought were hidden were actually deleted. Maybe two are very-hidden and you need to decide whether to reveal them. The message box turns a blind operation into an auditable one.

#Very-hidden sheets: when to reveal them

If you do want to unhide very-hidden sheets — for example, you’re auditing a workbook and need to see everything — change the xlSheetVeryHidden case to match the xlSheetHidden case:

Case xlSheetVeryHidden
    ws.Visible = xlSheetVisible
    veryHidden = veryHidden + 1

But be thoughtful about it. Very-hidden sheets often contain data validation lists, lookup tables, or macro state that was never meant to be seen. Unhiding them can clutter the tab bar with sheets named things like "_PivotCache_12" or "_Settings_". The macro ships with the conservative default for a reason.

#Adapt It