Comment Collector: Pull Every Review Note into One Printable Report

TL;DR: Before you archive a completed engagement, run this macro. It extracts every preparer note, reviewer question, and partner sign-off scattered across 30 tabs into one searchable, printable report with clickable hyperlinks back to each source cell. Your original workpaper stays untouched.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with cell comments (the red triangle indicators in the corner of cells)
• Output is a new “Comments” sheet, created at the end of the workbook — your original data is never touched

Limitations:

• Only collects traditional cell comments — not threaded comments (modern Excel’s reply-thread system, introduced in Excel 365). Threaded comments use a different object model and are not included in the Comments collection.
• Comment text is plain text only — formatting, shapes, and dimensions are not preserved in the report
• If a “Comments” sheet already exists from a previous run, it is deleted and replaced

#The Macro

Option Explicit

Sub CommentCollector()
    ' ── Comment Collector ──────────────────────────────
    ' Scans every comment on every sheet and compiles
    ' them into a new "Comments" sheet with hyperlinks
    ' back to each cell. Zero input. Does not modify
    ' original data.
    ' ──────────────────────────────────────────────────

    Const OUT_SHEET As String = "Comments"

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim cmt As Comment
    Dim outRow As Long
    Dim totalComments As Long
    Dim sheetCount As Long

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

    ' ── Count before building output ───────────────────
    totalComments = 0
    sheetCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Comments.Count = 0 Then GoTo NextCount
        sheetCount = sheetCount + 1
        totalComments = totalComments + ws.Comments.Count
NextCount:
    Next ws

    If totalComments = 0 Then
        MsgBox "No comments found in this workbook.", vbInformation
        GoTo CleanUp
    End If

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

    ' ── Create output sheet ────────────────────────────
    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Write headers ──────────────────────────────────
    wsOut.Range("A1:D1").Value = Array( _
        "Sheet", "Cell", "Author", "Comment")
    With wsOut.Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
    End With

    ' ── Collect comments ───────────────────────────────
    outRow = 2

    For Each ws In ThisWorkbook.Worksheets
        If ws.Comments.Count = 0 Then GoTo NextSheet

        For Each cmt In ws.Comments
            ' Sheet name
            wsOut.Cells(outRow, 1).Value = ws.Name

            ' Hyperlinked cell address
            wsOut.Hyperlinks.Add _
                Anchor:=wsOut.Cells(outRow, 2), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!" & _
                    cmt.Parent.Address(False, False), _
                TextToDisplay:=cmt.Parent.Address(False, False)

            ' Author
            wsOut.Cells(outRow, 3).Value = cmt.Author

            ' Comment text
            wsOut.Cells(outRow, 4).Value = cmt.Text

            outRow = outRow + 1
        Next cmt

NextSheet:
    Next ws

    ' ── Format output ──────────────────────────────────
    With wsOut
        .Columns("A:D").AutoFit
        .Columns("D").ColumnWidth = 55
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
    End With

    MsgBox "Found " & totalComments & " comment(s) across " & _
           sheetCount & " sheet(s)." & vbCrLf & vbCrLf & _
           "Report created on '" & OUT_SHEET & "' sheet.", _
           vbInformation, "Comment Collector"

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

#How It Works

#Count first, build second

The macro makes two passes. The first pass counts how many comments exist across all sheets. If the count is zero, it exits immediately with a clean message — no empty output sheet to delete later. If comments are found, it reports the count in the MsgBox so you can mentally verify (“42 comments? That sounds about right for this engagement”).

For Each ws In ThisWorkbook.Worksheets
    If ws.Comments.Count = 0 Then GoTo NextCount
    sheetCount = sheetCount + 1
    totalComments = totalComments + ws.Comments.Count
NextCount:
Next ws

This two-pass approach also means you get an accurate count in the MsgBox before you click OK. If you expected 50 comments and the macro says 42, you know to investigate which sheet’s comments are missing before archiving.

#The Comments collection — not SpecialCells

Excel offers multiple ways to find comments, but ws.Comments is the only one that gives you author and text in one step. SpecialCells(xlCellTypeComments) returns a Range — you’d still need to dig each comment out of each cell individually. The Comments collection iterates cleanly and skips sheets with no comments automatically (Count = 0).

Each Comment object exposes:

• .Parent — the Range the comment is attached to (cell A2, B14, etc.)
• .Author — the username of whoever created it
• .Text — the full comment body

#Hyperlinks that survive sheet renames

wsOut.Hyperlinks.Add _
    Anchor:=wsOut.Cells(outRow, 2), _
    Address:="", _
    SubAddress:="'" & ws.Name & "'!" & _
        cmt.Parent.Address(False, False), _
    TextToDisplay:=cmt.Parent.Address(False, False)

The Address is empty (no external file), and SubAddress points directly to the source cell. The single quotes around ws.Name handle tabs with spaces or special characters — 'Sched-A Income'!B6 is a valid Excel reference, but Sched-A Income!B6 is not.

Address(False, False) returns the cell reference without dollar signs (B6 instead of $B$6), keeping the report clean and readable.

#Output at the end — out of the way

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

The “Comments” sheet goes at the end of the workbook, not the beginning. This is a review artifact, not the primary workpaper. When you open the file to review the actual schedules (Sched-A, Fixed Assets), the Comments tab sits quietly at the far right where it doesn’t interfere with your workflow.

Contrast with the auto-table-of-contents macro, which places the TOC sheet first because it IS the primary navigation tool. Different purpose, different position.

#The cleanup label always runs

Every On Error GoTo CleanUp in this macro routes to the same label. If the macro hits an error — a protected sheet blocking access, a corrupted comment object, anything — the cleanup fires and restores ScreenUpdating and Calculation. A crashed macro that leaves Excel in manual calculation mode generates support tickets. The cleanup prevents that.

#Why a message box with counts

Silent macros are dangerous. The MsgBox confirms what happened and how much:

MsgBox "Found " & totalComments & " comment(s) across " & _
       sheetCount & " sheet(s)." & vbCrLf & vbCrLf & _
       "Report created on '" & OUT_SHEET & "' sheet.", _
       vbInformation, "Comment Collector"

If you expected 8 sheets with comments but the macro says 5, you know to check which 3 sheets were skipped. If an earlier preparer left only 3 comments on a 50-tab workbook, the count tells you that without scrolling through the report.

#Adapt It