Copy Cell Comments Automatically To Another WorkSheet

Please Login or Register  to view this content.

Using the above code: The above code Marks changes made in one worksheet and when you scroll over it with you mouse it tells user what changes had been made to that cell. How do I copy those changes and paste them into another cell on the second worksheet. Lets say into A10. I would appreciate any help or if you need anything else from me please let me know.

hi,

Here's a code I use for copying comments to cells on the current sheet, but it can be modified by changing the Set "rngOutput = Cells(rngCmt.Row, rngOutputCol)" line - it was originally sourced from the link commented within the macro...

Sub TOC_Comments()
'http://excelforum.com/showthread.php?t=536733
Dim rngCmt As Range
Dim rngCmts As Range
Dim rngOutput As Range
Dim rngOutputCol As Long
rngOutputCol = InputBox("Please enter the number of empty column for inserting comments into", _
"PLEASE INSERT COMMENTS INTO COLUMN...")
Application.ScreenUpdating = False
Set rngCmts = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
MsgBox rngCmts.Count
For Each rngCmt In rngCmts.Cells
If rngCmt.Row = 1 Then
'To ignore header row
GoTo CONTINUE
End If
Set rngOutput = Cells(rngCmt.Row, rngOutputCol)
'MsgBox rngOutput.Address ' used as a check when testing
If IsEmpty(rngOutput) Then
rngOutput = rngCmt.Address & ": " & rngCmt.Comment.Text
Else
rngOutput.Offset(0, 1) = rngCmt.Address & ": " & rngCmt.Comment.Text
End If
''to delete comments
'Cells(rngCmt.Row, rngCmt.Column).ClearComments

CONTINUE:
Next
Set rngOutput = Nothing
Set rngCmts = Nothing
Application.ScreenUpdating = True
End Sub

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Thank You for the post.


I am vb and coding stupid so please bear with me. What does that code do??? I understand the declaring of the veriables. After the DIM statments. Will this code ask the user for information or will it attomacitly grab the changes that have been made to the sheets and insert them in the the second worksheet.

Please Login or Register  to view this content.

Such as the information that that code aboves. THat is the information i need to be imputted into a sell on the second worksheet.