Hi everyone,
If a new value is entered in a range of A1:A10, would it be possible to log previous values of these cells as well as the new value on a separate sheet by change event?
Any ideas would be greatly appreciated.
Thanks & regards
KP
Hi everyone,
If a new value is entered in a range of A1:A10, would it be possible to log previous values of these cells as well as the new value on a separate sheet by change event?
Any ideas would be greatly appreciated.
Thanks & regards
KP
Something like
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Sheets(2).Range(Target.Address).Offset(, 1) = Target.Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Sheets(2).Range(Target.Address) = Target.Value
End If
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi Mdmackillop,
Yes. Your code is what exactly I'm after. Thanks so much for your help.
Have a nice w/end.
Regards
KP
Hi everyone,
I marked this thread as solved. However, I just come across a situation in which the above code does not seem to be working properly. When I copy a value from A1 by dragging the mouse from cell A1's bottom right corner to A2:A10, I end up with same values in the new and previous amounts. Would there be any modification required? Also, would it possible to capture changes in the whole sheet 1 rather than A1:A10?
Any help would be very appreciated.
Thanks & regards
KP