Comments can be a great way to add a small bit of documentation to your spreadsheets. If your spreadsheet gets too big and with many sheets then important comments can easily get lost somewhere in the workbook and forgotten about. Here we’ll show you an easy way to get all the comments in a workbook. We will also add a hyperlink to the original location of the comment for ease of tractability.

First create a new sheet called “Comments” for the purpose of writing out all the comments. Then add this bit of code into the visual basic editor.

Sub GetAllComments()

Dim WS As Worksheet
Dim Cmnt As Comment
Dim Count As Long

Count = 0

For Each WS In ActiveWorkbook.Worksheets
    For Each Cmnt In WS.Comments
        Worksheets("Comments").Range("A1").Offset(Count, 0).Parent.Hyperlinks.Add _
            Anchor:=Worksheets("Comments").Range("A1").Offset(Count, 0), _
            Address:="", _
            SubAddress:="'" & WS.Name & "'!" & Cmnt.Parent.Address, _
            TextToDisplay:="'" & WS.Name & "'!" & Cmnt.Parent.Address
        Worksheets("Comments").Range("B1").Offset(Count, 0).Value = Cmnt.Author
        Worksheets("Comments").Range("C1").Offset(Count, 0).Value = Cmnt.Text
        Count = Count + 1
    Next Cmnt
Next WS

End Sub

Now all you have to do is run the code and all your comments will appear in the sheet you created called “Comments” and you will be able to click the hyperlink to go to the cell containing the comment.