This Excel tutorial explains how to consolidate all Comments in the workbook using Macro.
You may also want to read:
Excel show all comments in worksheet
Excel delete all comments in Workbook
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.
Outbound References