Tracking Comments

[reza_doang]

Hi All,

i have a workbook that will share to anyone and i got macro from this group about tracking changes with comment.
Now, i have a workbook with many sheets and maybe have many changes with comment, so i thing i need macro that will create a new sheet and pull out all comments in new sheet.
i attach my sample, in that sample i have 2 sheet and comments.
after run macro, it will create a new sheet with sheet name "comments".
for more detail you can see the attachment.

many thanks for your help

reza

[Aussiebear]

Try the following section of code from Debra Dalgliesh's website http://www.contextures.com

List Comments on New Sheet

Sub showcomments()
  'posted by Dave Peterson 2003-05-16
      Application.ScreenUpdating = False
Dim commrange As Range
      Dim cmt As Comment
      Dim curwks As Worksheet
      Dim newwks As Worksheet
      Dim i As Long
Set curwks = ActiveSheet
On Error Resume Next
      Set commrange = curwks.Cells _
          .SpecialCells(xlCellTypeComments)
      On Error GoTo 0
If commrange Is Nothing Then
    MsgBox "no comments found"
    Exit Sub
      End If
Set newwks = Worksheets.Add
newwks.Range("A1:E1").Value = _
           Array("Number", "Name", "Value", "Address", "Comment")
i = 1
      For Each cmt In curwks.Comments
    With newwks
        i = i + 1
        On Error Resume Next
        .Cells(i, 1).Value = i - 1
        .Cells(i, 2).Value = cmt.Parent.Name.Name
        .Cells(i, 3).Value = cmt.Parent.Value
        .Cells(i, 4).Value = cmt.Parent.Address
        .Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ")
    End With
      Next cmt
newwks.Cells.WrapText = False
      newwks.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

This should give you a good start.

[reza_doang]

aussie...

work great...
btw i want to ask little, what for the column name?
why i get empty for that?

thanks

[Aussiebear]

"What for the column name"? & "Why I get empty for that"?

Can you explain in greater detail please, I've just come off a 14 hour shift, so go easy.