Copy Cell Comments Automatically To Another WorkSheet

[centaur63]

Public preValue As Variant 'Declares a variable to be used in both macros
 'This is the first line for a macro that runs when a cell is changed.
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 'Limits the change to 1 cell only
    If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub 
    Target.ClearComments 'Clears any existing comment
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 'Adds a new comment with the text. CHR(10) is a return.
End Sub 
 
 'This code sets the variable preValue when a cell is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 'Limits code to 1 cell
    If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub 
    preValue = Target.Value 'Set the variable preValue to the selected cell's value
End Sub

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.

 

[centaur63]

Should I Use

Dim wb As Workbook 
 
Set wb = MyOtherWorkbook.xls 
 
 '
Cells.Copy 
wb.Activate 
wb.Sheet1.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _ 
SkipBlanks:=False, Transpose:=False 
Range("J20").Select

Should I use something like that?? IM not sure where to put this code or if this is correct or not. I would appreciate any comments or help on this.

 

[centaur63]

OK

If there is anything else you guys need to know please let me know.