Hi,

the code will now look for a sheet called "Comments" and copy all the
comments to that and add the date/time and the workbook name

Sub ShowCommentsAllSheets()
'modified from code
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim ws As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set newwks = Sheets("comments")
If newwks.Range("a1") = "" Then
newwks.Range("A1:G1").Value = _
Array("Sheet", "Address", "Name", "Value", "Comment", "Date", "Workbook")
End If

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
'do nothing
Else

i = newwks.Cells(Rows.Count, 1).End(xlUp).Row

For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Name.Name
.Cells(i, 4).Value = mycell.Value
.Cells(i, 5).Value = mycell.Comment.Text
.Cells(i, 6).Value = Now()
.Cells(i, 7).Value = ActiveWorkbook.Name
End With
Next mycell
End If
Set commrange = Nothing
Next ws

'format cells for no wrapping, remove line break
newwks.Cells.WrapText = False
newwks.Columns("E:E").Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Application.ScreenUpdating = True

End SubMike

"dmars" wrote:

Mike thanks for getting to me so quickly. I ran the macro and it works. The
only problem is that it creates a new sheet every time I run it. I need to
have one sheet with all comments. At some point I will delete the comments
in the worksheet but still need to have them archived in the history log. I
really don't know much about VBA so I appreciate your help. It would also be
helpful if I could get the date and file name to go into the history log. At
the end of every month I version up the workbook and delete the comments for
that month but the history log should maintain all comments and allow me to
add to them.

Thanks again.

"Mike H" wrote:

Have a look here

http://www.contextures.com/xlcomment...ml#CopyToSheet

Mike

"dmars" wrote:

Hi,

Can anyone help me with the code needed to copy all my comments to another
worksheet(history log) in the same workbook? Comments will need to be
appended to the worksheet as they are added. I was able to find code that
would copy all coments to a new sheet but everytime I run it it creates a new
sheet and replaces the file. It doesn't add to the existing worksheet. I am
trying to create a history log of all comments. When a comment is deleted
from the spreadsheet it should remain on the history log and if a comment is
added it should append to the worksheet.

Thanks for your help.