How to list all comments in the current workbook to a new worksheet or workbook in Excel?

If you have workbook with too many comments in it, and you want to list all comments to a new worksheet or workbook for reviewing all comments, how can ou do? The following methods can help you quickly list all comments of a worksheet / workbook to a new worksheet or workbook in Excel.

Use VBA code to list all comments in the current workbook to a new worksheet

Use Kutools for Excel to list all comments in the current workbook to a new worksheet or workbook

Use VBA code to list all comments in the current workbook to a new worksheet

The following VBA code will list all comments in the current workbook to a new worksheet. In the new worksheet, it will list all comments of the whole workbook in a list with the sheet name, cell address, comment contents and cell name. Please use this method as follows:

1. Click "Developer" > "Visual Basic", it displays the "Microsoft Visual Basic for applications" window. And click "Insert" > "Module", and then input the following VBA codes in the Module.

VBA: lists all comments in a new worksheet.

Sub ShowCommentsAllSheets()
'Update 20140508
Dim commrange As Range
Dim rng As Range
Dim ws As Worksheet
Dim newWs As Worksheet
Set newWs = Application.Worksheets.Add
newWs.Range("A1").Resize(1, 4).Value = Array("Sheet", "Address", "Value", "Comment")
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In Application.ActiveWorkbook.Worksheets
    Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
    If Not commrange Is Nothing Then
        i = newWs.Cells(Rows.Count, 1).End(xlUp).Row
        For Each rng In commrange
            i = i + 1
            newWs.Cells(i, 1).Resize(1, 4).Value = Array(ws.Name, rng.Address, rng.Value, rng.Comment.Text)
        Next
    End If
    Set commrange = Nothing
Next
newWs.Cells.WrapText = False
Application.ScreenUpdating = True
End Sub

2. Then click  button to run the code, and you will get a new worksheet with all the comments in the current workbook. See screenshot:

Use Kutools for Excel to list all comments in the current workbook to a new worksheet or workbook

With the "Create Comment List" of "Kutools for Excel", you can quickly list all comments to a new worksheet or a new workbook.

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.

1. If you have installed Kutools for Excel, click "Kutools" > "More" > "Create Comment List."

2. In the "Create Comment List" dialog box:

• Select the scope that you want to list the comment, you can choose "Active sheet" or "All sheets".
• Then specify the location that you want to put the comments list, you can list the comments in a new workbook or a new sheet, see screenshot:

3. And then click "Create" button. A new worksheet will be added to list all the active worksheet comments with its attributes such as sheet name, cell references, row and column. See screenshot:

For more detailed information about "Create Comment List", please visit Create Comment List feature description.

Related articles:

• Change all comment formats in cells
• Change all comments' author name
• Find and replace text within comments
• Show or hide all comments and comment indicators in Excel
• Convert cell contents to comments in Excel
• Convert comments to cell contents in Excel