Comment Macro

[Viking1221]

Hello,

I have a workbook with multiple tabs (40+). Each tab is set up the exact same way, there is just a tab for each location and shows their P&L. The first tab is a consolidated tab (same format as all others). Each tab shows a monthly trend and variance to goals. I add comments to each tab as I work through it highlighting the reasoning for the variances by row. I would like to have a macro that would search all the tabs (2-40) that would aggregate all my comments into the consolidated tab in the same cell it was added in the supporting tabs. For example, if I added a comment on tab 3 called "location 4" and tab 7 called "location 8" in cell C25 I would like the macro to search all the tabs and consolidate the comments into the "total' tab. The comment would read Location 4 - "XXXX" Location 8 - "YYY"

Is that even possible?

Thanks.

 

[Legacy 143009]

Hi,

I think this should work:

Sub test()
  Dim totalRange As Range, varComment As String, tempComment As String, c As Comment
  Set totalRange = Worksheets("Total").UsedRange
 
  For Each ws In Worksheets
    If ws.Name <> "Total" Then
      For Each Rng In totalRange
        With ws.Range(Rng.Address)
        On Error Resume Next
        Set c = .Comment
        On Error GoTo 0
        If Not c Is Nothing Then
          varComment = c.Text
          If Not Rng.Comment Is Nothing Then
            tempComment = Rng.Comment.Text
            Rng.Comment.Delete
          End If
          Rng.AddComment tempComment & ws.Name & " - """ & varComment & """ "
        End If
        End With
      Next
    End If
  Next
End Sub