If you have a huge dataset and the comments are scattered all over the worksheet, it may help to have all comments in a single place as a list.

Get a List of All the Comments in a Worksheet

In this tutorial, I will show you how to use a VBA code to get a list of all the comments in a Worksheet in a separate worksheet. Something as shown below:

There is a VBA code in the backend that does the following things:

It checks whether there are any comments in the active worksheet or not. If there are no comments, it quietly bows out and does nothing. If there are comments in the worksheet, it creates a new worksheet (“Comments”) and extract a list of all the comments in the following structure: Column A has the cell address that has the comment. Column B has the commenter name. This comes in handy if there are multiple reviewers of the same file. It will also help filter/sort based on reviewers name. Column C has the comment.

Download the Example File

The VBA Code

Here is the VBA code that does all the heavy lifting here.

How to Use this Code

There are a couple of ways you can use this code to get a list of comments from your worksheet:

#1 Copy Paste Data in the Example File

Copy paste your data (as is) in the Data tab of the download file and then run this macro. To run this:

Go to the Developer tab and click on Macros. It will open the Macro dialogue box. Select ExtractComment and click on Run. Make sure you are in the sheet that has the comments that you wish to extract.

#2 Copy Paste the Code in Your Workbook

Copy the code and paste it in the workbook from which you want to extract the comments. To do this:

Activate the workbook in which you are working and press Alt + F11. This will open the VB Editor window. In the Project Explorer on the left, right-click on any of the objects for that workbook, go to Insert –> Module. Paste the code in the Module code window. Close the VB Editor Window (or press Alt + F11 to go back to the worksheet).

Now you have the macro in your workbook. To run the macro, go to Developer Tab –> Macros. In the Macro dialogue box, select the ExtractComment macro and click in Run. Note: Make sure you save the workbook with .XLS or .XLSM extension.

#3 Create an Add-in

If you need to use this code often, it’s best to create an add-in out of it. That way you can easily use it in any workbook (without the additional effort of copy pasting the code again and again). Here is how to create an Add-in:

Go to File –> Save As. In the Save As dialogue box, change the Save as type to .xlam. You’ll notice that the path of the file where it gets saved automatically changes. You can change it if you want. Open an Excel workbook and Go to Developer –> Add-ins –> Excel Add-ins. In the Add-ins dialogue box, browse and locate the file that you saved, and click OK.

Once an add-in has been activated, you can use it in any workbook. To do that, go to Developer –> Macros. In the Macro dialogue box, select the ExtractComment macro and run it. Download the Example File Hope this code will save you some time. Let me know your thoughts in the comments section. If you work with Excel, VBA could be a powerful ally. Take your Excel Skills to the next level with the Excel VBA Course.

How to Insert a Picture in Excel Comment. How to Print Comments in Excel How to Insert a Picture in Excel Comment Excel VBA Tips and Tutorials.

Sub USEFUL_PrintCommentsAndNotes() If MsgBox(“Create a summary of comments and notes from the current workbook?”, vbOKCancel) = vbCancel Then Exit Sub ‘ “Review” type comments Dim AllCommentsThreaded As Excel.CommentsThreaded Dim OneCommentThreaded As Excel.CommentThreaded Dim AllReplies As Excel.CommentsThreaded Dim OneReply As Excel.CommentThreaded ‘ “Yellow” notes in excel Dim AllNotes As Excel.Comments Dim OneNote As Excel.Comment Dim WS, sh As Worksheet Dim r As Range Dim Flag As Boolean Dim i, totalsheets As Integer Dim LinkLocation As String Dim Linkloc, DataLoc, ComBy, ComIn, ComText, ComType As Integer ‘column locations ‘Create a comments work sheet or append to existing comments Flag = True ‘True if there is not already a worksheet with this name ‘Create a column location refernce for the data Linkloc = 1 DataLoc = 2 ComBy = 3 ComText = 4 ComType = 5 For Each sh In Worksheets If sh.Name = “Comments_Notes” Then Flag = False Set WS = sh ‘sets the reference worksheet to write comments to i = WS.Range(“A1”).CurrentRegion.Rows.Count + 1 ‘ get last row End If Next sh If Flag Then Set WS = Worksheets.Add(After:=Worksheets(Worksheets.Count)) WS.Name = “Comments_Notes” WS.Cells(1, Linkloc).Value = “Location Link” WS.Cells(1, DataLoc).Value = “Data at Location” WS.Cells(1, ComBy).Value = “Comment By” WS.Cells(1, ComText).Value = “Comment Text” WS.Cells(1, ComType).Value = “Type” i = 2 ‘new sheet, set comment row ‘Freeze panes for viewing ‘ActiveWindow.FreezePanes = True End If ‘Loop through each worksheet to gather comments and notes For Each sh In Worksheets If sh.Name = WS.Name Then ‘MsgBox (“No Comments Here”) ‘Exit For GoTo MoveAlong End If Set AllCommentsThreaded = sh.CommentsThreaded Set AllNotes = sh.Comments ‘ loop over all threaded comments of a worksheet and get their info For Each OneCommentThreaded In AllCommentsThreaded With OneCommentThreaded ‘Debug.Print .Parent.Address .Author.Name, .Date, OneReply.Text LinkLocation = “‘” & sh.Name & “‘!” & .Parent.Address ‘name of corresponding test tab” WS.Cells(i, Linkloc).Hyperlinks.Add _ Anchor:=WS.Cells(i, 1), _ Address:=””, _ SubAddress:=LinkLocation, _ TextToDisplay:=sh.Name & .Parent.Address WS.Cells(i, DataLoc) = “=” & LinkLocation WS.Cells(i, ComBy) = .Author.Name WS.Cells(i, ComText) = .Text WS.Cells(i, ComType) = “Comment” i = i + 1 For Each OneReply In .Replies With OneReply ‘Debug.Print .Author.Name, .Date, OneReply.Text ‘WS.Cells(i, 1) = sh.Name & OneCommentThreaded.Parent.Address WS.Cells(i, Linkloc).Hyperlinks.Add _ Anchor:=WS.Cells(i, 1), _ Address:=””, _ SubAddress:=LinkLocation, _ TextToDisplay:=sh.Name & OneCommentThreaded.Parent.Address WS.Cells(i, DataLoc) = “=” & LinkLocation WS.Cells(i, ComBy) = .Author.Name WS.Cells(i, ComText) = .Text WS.Cells(i, ComType) = “Comment Reply” i = i + 1 End With Next OneReply End With Next OneCommentThreaded ‘ loop over all Notes of a worksheet and get their info For Each OneNote In AllNotes With OneNote ‘WS.Cells(i, 1) = sh.Name & .Parent.Address LinkLocation = “‘” & sh.Name & “‘!” & .Parent.Address ‘name of corresponding test tab” WS.Cells(i, Linkloc).Hyperlinks.Add _ Anchor:=WS.Cells(i, Linkloc), _ Address:=””, _ SubAddress:=LinkLocation, _ TextToDisplay:=sh.Name & .Parent.Address WS.Cells(i, DataLoc) = “=” & LinkLocation WS.Cells(i, ComBy) = .Author WS.Cells(i, ComText) = .Text WS.Cells(i, ComType) = “Note” i = i + 1 End With Next OneNote MoveAlong: Next sh I have modified the code but you will need to adjust it based on your requirements.