Copy Cell Comments Automatically To Another WorkSheet

centaur63

New Member
Joined
Aug 31, 2006
Messages
42
Code:
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

centaur63

New Member
Joined
Aug 31, 2006
Messages
42
Should I Use

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,804
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top