Comment boxes are great when you want to share additional information about Excel data. You can view all comments on a worksheet or just specific comments you want to see. However, there may be times when you need to consolidate the comments in an Excel worksheet. In this post, we show you how to extract comments in an Excel Sheet using a macro.
If you have never created a macro before, you can learn how to do it in an easy way, without writing the codes yourself, using a macro recorder. You can read more about how to do that here.
HOW TO EXTRACT COMMENTS IN AN EXCEL SHEET
To extract comments in an Excel sheet and move it to another worksheet you need to use a User defined function. That is, custom functions that you can create yourself.
1. To get started, copy this code.
Sub ShowCommentsAllSheets() 'modified from code 'posted by Dave Peterson 2003-05-16 Application.ScreenUpdating = False Dim commrange As Range Dim mycell As Range Dim ws As Worksheet Dim newwks As Worksheet Dim i As Long Set newwks = Worksheets.Add newwks.Range("A1:E1").Value = _ Array("Sheet", "Address", "Name", "Value", "Comment") For Each ws In ActiveWorkbook.Worksheets On Error Resume Next Set commrange = ws.Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 If commrange Is Nothing Then 'do nothing Else i = newwks.Cells(Rows.Count, 1).End(xlUp).Row For Each mycell In commrange With newwks i = i + 1 On Error Resume Next .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = mycell.Address .Cells(i, 3).Value = mycell.Name.Name .Cells(i, 4).Value = mycell.Value .Cells(i, 5).Value = mycell.Comment.text End With Next mycell End If Set commrange = Nothing Next ws 'format cells for no wrapping, remove line break newwks.Cells.WrapText = False newwks.Columns("E:E").Replace What:=Chr(10), _ Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False Application.ScreenUpdating = True End Sub
2. Open the Excel 2010 worksheet containing the data with comments.
3. Press Alt + F11 to enter the Visual Basic Editor.
4. If necessary, press Ctrl + R to show the Project Explorer.
5. Right-click desired file on left (in bold).
6. Choose Insert -> Module.
7. Paste the code into the right pane.
8. Press Alt + Q to close the VBE.
9. Save the workbook in the XLSM format.
10. Select the Developer tab. (If you have a problem displaying the Developer tab, please read our post How to Activate the Developer Tab in Excel 2010.
11. On the Developer tab, in the Code group, click Macros.
12. In the Macro dialog box, select the macro and click Run. The comments in your datasheet will be extracted to a new sheet. You can then continue your analysis or other tasks associated with the extracted data.