David,

thanks again for all your help. I think I can work around the issues. I am
not concerned about retaing the cell address of the comment just a running
log of all comments that have been entered or edited onthe requirements
worksheet. The macro could just keep appending to the bottom of the history
sheet. I will just make sure to save the history log in a separate workbook
or file to prevent it from being deleted as the user deletes his/her comments.

Thanks Again!

"Dave Peterson" wrote:

Maybe you could just clear out any existing comments on that Comments sheet
before the real work starts.

Set newwks = Sheets("comments")
newwks.cells.clear '<-- added
If newwks.Range("a1") = "" Then


dmars wrote:

Wow! We are almost there. When i first ran the macro it copied all my
comments to the comments sheet. But when I ran the macro the second time to
capture any additional comments it duplicated all the comments. I ended up
with all comments entered twice. I only need to add or append any new
comments or changes to the comments to the comment sheet. You guys are
incredible. I can't tell you how much time you will save me.

"Mike H" wrote:

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.



--

Dave Peterson