COMMENTS copied to second TAB needed

[ddutton]

I have a rather large spreadsheet that is updated with various "comments". Could be 10, could be 100... Would like to ADD a second TAB that will show some of the DATA POINTS and the COMMENT that was entered.

For example: Col A is the name of the configuration item, column C is the location of the configuration item, and column G is the person on-call. Anywhere in the sheet (up to column M) can/will contain a COMMENT.

Would like to capture columns A, C, G, and the comment on the second sheet. Definitely will be a formula of some kind on the secondary sheet, just not sure where or how to gather the info. Any help would be MOST appreciated...

 

[Saurabhj]

Copy and paste will resolve the issue.

There is no formula to copy comments in excel. If you want to copy either value/ comment/ All, you can use option Ctrl + Alt + V, and select the required option.

Any other requirement which I am missing ?

 

[ddutton]

Here is my code (that does work, mind you). However, I would like to enhance this code to include same row data from some specific cells where the NOTE resides. I need the cell data from 2 or 3 other cells in the row where the note resides, to "ADD" to my notes list /report.. I hope this makes sense...

Sub LIST_NOTES()
Dim liComment As Comment
Dim i As Integer
Dim ws As Worksheet
Dim ComSh As Worksheet
Set ComSh = ActiveSheet
If ActiveSheet.Comments.Count = 0 Then Exit Sub

For Each ws In Worksheets
  If ws.Name = "Comments" Then i = 1
Next ws
   
If i = 0 Then
  Set ws = Worksheets.Add(After:=ActiveSheet)
  ws.Name = "Comments"
Else: Set ws = Worksheets("Comments")
End If

For Each liComment In ComSh.Comments
  ws.Range("A1").Value = "Located In Cell"
  ws.Range("B1").Value = "Author"
  ws.Range("C1").Value = "Note"
  With ws.Range("A1:C1")
    .Font.Bold = True
    .Interior.Color = RGB(189, 215, 238)
    .Columns.ColumnWidth = 20
  End With
  If ws.Range("A2") = "" Then
    ws.Range("A2").Value = liComment.Parent.Address
    ws.Range("B2").Value = Left(liComment.Text, InStr(1, liComment.Text, ":") - 1)
    ws.Range("C2").Value = Right(liComment.Text, Len(liComment.Text) - InStr(1, liComment.Text, ":"))
  Else
    ws.Range("A1").End(xlDown).Offset(1, 0) = liComment.Parent.Address
    ws.Range("B1").End(xlDown).Offset(1, 0) = Left(liComment.Text, InStr(1, liComment.Text, ":") - 1)
    ws.Range("C1").End(xlDown).Offset(1, 0) = Right(liComment.Text, Len(liComment.Text) - InStr(1, liComment.Text, ":"))
  End If
Next liComment
End Sub

 

[ddutton]

Copy and paste will resolve the issue.

There is no formula to copy comments in excel. If you want to copy either value/ comment/ All, you can use option Ctrl + Alt + V, and select the required option.

Any other requirement which I am missing ?

here is my updated code. I need to get the data in columns D and E to copy/paste into the second sheet if there is a NOTE. Everything else works great... but somehow, I believe that I need some code added to the bottom series of strings, after the formatting section... thank you so much for your help...

Sub LIST_NOTES()
Dim liComment As Comment
Dim i As Integer
Dim ws As Worksheet
Dim ComSh As Worksheet
Set ComSh = ActiveSheet
If ActiveSheet.Comments.Count = 0 Then Exit Sub

For Each ws In Worksheets
  If ws.Name = "Comments" Then i = 1
Next ws
    
If i = 0 Then
  Set ws = Worksheets.Add(After:=ActiveSheet)
  ws.Name = "Comments"
Else: Set ws = Worksheets("Comments")
End If

For Each liComment In ComSh.Comments
  ws.Range("A1").Value = "Located In Cell"
  ws.Range("B1").Value = "Author"
  ws.Range("C1").Value = "Note"
  ws.Range("D1").Value = "Configuration Instance"
  ws.Range("E1").Value = "Support Group"
  
  With ws.Range("A1:E1")
    .Font.Bold = True
    .Interior.Color = RGB(189, 215, 238)
    .Columns.ColumnWidth = 20
  End With
  If ws.Range("A2") = "" Then
    ws.Range("A2").Value = liComment.Parent.Address
    ws.Range("B2").Value = Left(liComment.Text, InStr(1, liComment.Text, ":") - 1)
    ws.Range("C2").Value = Right(liComment.Text, Len(liComment.Text) - InStr(1, liComment.Text, ":"))
  Else
    ws.Range("A1").End(xlDown).Offset(1, 0) = liComment.Parent.Address
    ws.Range("B1").End(xlDown).Offset(1, 0) = Left(liComment.Text, InStr(1, liComment.Text, ":") - 1)
    ws.Range("C1").End(xlDown).Offset(1, 0) = Right(liComment.Text, Len(liComment.Text) - InStr(1, liComment.Text, ":"))
  End If
Next liComment
End Sub