This Excel tutorial explains how to consolidate all Comments in the workbook using Macro.
You may also want to read:
Excel consolidate all Comments in the workbook
Previously I wrote a post about how to show all Comments on the Cell (unhide it), in this tutorial I will show a Macro that consolidate all comments in the workbook.
VBA Code – consolidate all comments
Public Sub consolComments() counter = 1 On Error Resume Next Application.DisplayAlerts = False Sheets("show comments").Delete Application.DisplayAlerts = True On Error GoTo 0 Set newWS = ActiveWorkbook.Worksheets.Add newWS.Name = "show comments" newWS.Range("A1").Value = "Worksheet" newWS.Range("B1").Value = "Address" newWS.Range("C1").Value = "Value" newWS.Range("D1").Value = "Comment" For Each ws In ActiveWorkbook.Sheets If ws.Name <> "show comments" Then Set allcommentrng = Nothing On Error Resume Next Set allcommentrng = ws.Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 If Not allcommentrng Is Nothing Then For Each Rng In allcommentrng counter = counter + 1 newWS.Range("A" & counter).Value = ws.Name newWS.Range("B" & counter).Value = Rng.Address newWS.Range("C" & counter).Value = Rng.Value newWS.Range("D" & counter).Value = Rng.Comment.Text Next Rng End If End If Next ws If Sheets("show comments").Range("A2").Value = "" Then Application.DisplayAlerts = False Sheets("show comments").Delete Application.DisplayAlerts = True MsgBox ("No comments in this workbook") End If End Sub Function wsExists(wksName As String) As Boolean On Error Resume Next wsExists = CBool(Len(Worksheets(wksName).Name) > 0) On Error GoTo 0 End Function
Explanation of VBA Code
– delete worksheet “show comments”, if any
– create worksheet “show comments” with headers
– for each worksheet, name all the Comments Cell as allcommentrng
– if there are Comments, write down in “show comments”
– delete “show comments” if it contains no data
Demonstration – consolidate all comments
In the below example, there are two worksheets that contain Comments. Our goal is to consolidate the Comments in both worksheets in a new worksheet.
Run the Macro, then a worksheet “show comments” is created in the end. The “Value” field is the value in the Comment Cell.