One way:

Option Explicit
Sub testme()

Dim cmt As Comment
Dim wks As Worksheet
Dim rptWks As Worksheet
Dim DestCell As Range

Set rptWks = Worksheets.Add
With rptWks
.Range("a1").Resize(1, 3).Value _
= Array("Sheet Name", "Address", "Text")
Set DestCell = rptWks.Range("a2")
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = rptWks.Name Then
'do nothing
Else
For Each cmt In wks.Comments
DestCell.Value = "'" & wks.Name
DestCell.Offset(0, 1).Value = cmt.Parent.Address(0, 0)
DestCell.Offset(0, 2).Value = cmt.Text
Set DestCell = DestCell.Offset(1, 0)
Next cmt
End If
Next wks

End Sub

ps. Debra Dalgleish has some code that you may want to review:
http://www.contextures.com/xlcomments03.html#CopyToWord

It wouldn't be difficult to loop through the worksheets.

Kenshe wrote:

Otto,
Works great, your help and knowledge is much appreciated to a novice
like me.

If possible, I have one more request,

I am looking for a way to gather up all the "comments" into a single
report sheet. I have 29 sheets in the work book, the 30th sheet is
where I would like to copy any comments we may have. The cell range
where a comment can only be put (from the 29sheets) is B25:IV29.
Right now I use Word imbedded into sheet 30 and copy and paste. so, I
am looking for an easier way to compile all the comments to read in a
single report. Word works good for this task, but the time it takes to
copy and paste is the issue.

Thanks much!

Ken

--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

--

Dave Peterson