Workbook Inventory: Map Every Sheet in an Inherited File in 3 Seconds

TL;DR: You inherit a 20-tab workpaper. Which sheets are hidden? Which have formulas? Which are protected? Where does the data actually end? This macro answers all five questions in one click, building a hyperlinked inventory sheet with every detail you need to triage the file before you touch a single number.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• Any workbook — including password-protected workbooks (the macro reads, not writes)

What the macro does NOT do:

• It does not modify any cell on any existing sheet. It creates one new “Inventory” sheet and writes only there.
• It does not unhide, unprotect, or change anything in the workbook. It’s read-only.
• It does not crack passwords. If a sheet is protected, it tells you — it doesn’t bypass it.

Limitations:

• Row count is measured in column A (the most reliable column in a tax workpaper). If your file has data in columns B-Z but nothing in A, the count will be low.
• Tab color is reported as a hex code, not a name. Excel doesn’t store color names, just numeric codes. “None” means no color applied.
• The Has Formulas? check uses SpecialCells(xlCellTypeFormulas) which may count cells with formula errors (#REF!, #N/A) as formulas. They are formulas — just broken ones.

#The Macro

Option Explicit

Sub WorkbookInventory()
    ' ── Workbook Inventory ─────────────────────────────
    ' Creates an "Inventory" sheet listing every sheet in
    ' the workbook with its name, visibility status,
    ' dimensions, formula presence, protection, and tab
    ' color. The first thing you run when you inherit
    ' someone else's workbook.
    '
    ' No input. No configuration. Run on any workbook.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Inventory"

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim r As Long
    Dim lastRow As Long, lastCol As Long
    Dim lastCellAddr As String
    Dim hasFormulas As String, isProtected As String
    Dim tabColorHex As String, visStatus As String
    Dim sheetCount As Long

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

    ' ── Create/refresh output sheet ────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Headers ────────────────────────────────────────
    wsOut.Range("A1:H1").Value = Array( _
        "#", "Sheet Name", "Visibility", "Row Count", _
        "Col Count", "Last Cell", "Has Formulas?", _
        "Protected?")
    wsOut.Range("A1:H1").Font.Bold = True
    wsOut.Range("A1:H1").Interior.Color = RGB(50, 50, 50)
    wsOut.Range("A1:H1").Font.Color = vbWhite

    ' ── Scan every sheet ───────────────────────────────
    r = 2
    sheetCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = OUT_SHEET Then GoTo NextSheet

        sheetCount = sheetCount + 1

        ' ── Visibility ─────────────────────────────────
        Select Case ws.Visible
            Case xlSheetVisible:    visStatus = "Visible"
            Case xlSheetHidden:     visStatus = "Hidden"
            Case xlSheetVeryHidden: visStatus = "Very Hidden"
        End Select

        ' ── Row count (last used row in column A) ──────
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

        ' ── Column count (last used column in row 1) ───
        lastCol = ws.Cells(1, ws.Columns.Count). _
            End(xlToLeft).Column

        ' ── Last cell address ──────────────────────────
        If lastRow > 1 Or lastCol > 1 Then
            lastCellAddr = ColLetter(lastCol) & lastRow
        Else
            lastCellAddr = "(empty)"
        End If

        ' ── Has formulas? ──────────────────────────────
        On Error Resume Next
        hasFormulas = IIf(ws.Cells.SpecialCells( _
            xlCellTypeFormulas).Count > 0, "Yes", "No")
        If Err.Number <> 0 Then hasFormulas = "No"
        On Error GoTo CleanUp

        ' ── Protected? ─────────────────────────────────
        isProtected = IIf(ws.ProtectContents, "Yes", "No")

        ' ── Tab color ──────────────────────────────────
        If ws.Tab.Color = 0 Or ws.Tab.Color = xlNone Then
            tabColorHex = "None"
        Else
            tabColorHex = Right$("000000" & _
                Hex(ws.Tab.Color), 6)
        End If

        ' ── Write row ──────────────────────────────────
        wsOut.Cells(r, 1).Value = sheetCount
        wsOut.Cells(r, 1).HorizontalAlignment = xlCenter
        wsOut.Cells(r, 1).Font.Color = RGB(140, 140, 140)

        ' Hyperlinked sheet name
        wsOut.Hyperlinks.Add _
            Anchor:=wsOut.Cells(r, 2), _
            Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", _
            TextToDisplay:=ws.Name
        wsOut.Cells(r, 2).Font.Size = 10

        wsOut.Cells(r, 3).Value = visStatus
        wsOut.Cells(r, 4).Value = lastRow
        wsOut.Cells(r, 5).Value = lastCol
        wsOut.Cells(r, 6).Value = lastCellAddr
        wsOut.Cells(r, 7).Value = hasFormulas
        wsOut.Cells(r, 8).Value = isProtected

        ' Color-code hidden sheets
        Select Case visStatus
            Case "Hidden"
                wsOut.Cells(r, 3).Interior.Color = _
                    RGB(254, 240, 138)
            Case "Very Hidden"
                wsOut.Cells(r, 3).Interior.Color = _
                    RGB(254, 202, 202)
        End Select

        ' Alternate row shading
        If sheetCount Mod 2 = 0 Then
            wsOut.Range("A" & r & ":H" & r). _
                Interior.Color = RGB(248, 248, 250)
        End If

        r = r + 1
NextSheet:
    Next ws

    ' ── Summary row ────────────────────────────────────
    wsOut.Cells(r, 2).Value = _
        "TOTAL: " & sheetCount & " sheet(s)"
    wsOut.Cells(r, 2).Font.Bold = True
    wsOut.Cells(r, 2).Font.Italic = True
    wsOut.Range("A" & r & ":H" & r). _
        Borders(xlEdgeTop).LineStyle = xlContinuous

    ' ── Format output ──────────────────────────────────
    With wsOut
        .Columns("A").ColumnWidth = 5
        .Columns("B").ColumnWidth = 32
        .Columns("C").ColumnWidth = 14
        .Columns("D").ColumnWidth = 12
        .Columns("E").ColumnWidth = 11
        .Columns("F").ColumnWidth = 14
        .Columns("G").ColumnWidth = 15
        .Columns("H").ColumnWidth = 12
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
    End With

    MsgBox sheetCount & " sheet(s) inventoried." & _
        vbCrLf & vbCrLf & _
        "See the '" & OUT_SHEET & "' sheet.", _
        vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

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

' ── Helper: Column number → letter ────────────────────
Private Function ColLetter(colNum As Long) As String
    ColLetter = Split( _
        Cells(1, colNum).Address(True, False), "$")(0)
End Function

#How It Works

#The “first thing you run” principle

Every column answers a question the preparer has before they start work:

• Visibility: Are there hidden sheets nobody told me about?
• Row Count: How big is this sheet? Is it 5 rows or 500?
• Last Cell: Where does the data end? Did they overshoot?
• Has Formulas?: Should I trust the numbers or audit the logic?
• Protected?: Do I need to ask someone for a password before I can edit?

The inventory answers all of them at once. It turns a 20-tab mystery into a single-page scan.

#Hidden sheets get a yellow warning, very-hidden gets red

Case xlSheetHidden
    wsOut.Cells(r, 3).Interior.Color = RGB(254, 240, 138)
Case xlSheetVeryHidden
    wsOut.Cells(r, 3).Interior.Color = RGB(254, 202, 202)

xlSheetVeryHidden means the sheet won’t appear in Excel’s normal “Unhide” dialog. It can only be unhidden through VBA or the VB Editor’s Properties window. A preparer clicking Format → Hide & Unhide → Unhide Sheet won’t see it. This is the most dangerous kind of hidden — and the inventory flags it in red.

#The formula check handles errors gracefully

On Error Resume Next
hasFormulas = IIf(ws.Cells.SpecialCells( _
    xlCellTypeFormulas).Count > 0, "Yes", "No")
If Err.Number <> 0 Then hasFormulas = "No"
On Error GoTo CleanUp

SpecialCells(xlCellTypeFormulas) throws an error if there are zero formula cells anywhere on the sheet. Rather than wrap the entire scan in an error handler, the macro temporarily swallows the error, checks the count, and restores normal error handling. If the error fires, there are no formulas — hasFormulas = "No" and we move on.

This pattern — On Error Resume Next for a specific dangerous operation, immediately restoring On Error GoTo CleanUp — isolates risk to a single line. One operation can fail without crashing the whole macro.

#Tab color: why the hex code and not a name

If ws.Tab.Color = 0 Or ws.Tab.Color = xlNone Then
    tabColorHex = "None"
Else
    tabColorHex = Right$("000000" & _
        Hex(ws.Tab.Color), 6)
End If

Excel stores tab colors as 24-bit RGB integers. ws.Tab.Color for a red tab returns 255 (the decimal encoding of #0000FF — yes, Excel reverses the byte order). The Hex() function converts this to a 6-character hex string like "0000FF". The Right$("000000" & ...) pads with leading zeros so colors like black (0 → "000000") don’t show up as a single digit.

In practice, you’ll see values like "FF0000" (red) and "008000" (green). The inventory reports what’s actually there — if the prior preparer used a color, you’ll see it.

#Adapt It