PDA

View Full Version : Solved: Logging previous values in series of cells



Digita
06-30-2007, 02:31 AM
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

mdmackillop
06-30-2007, 02:50 AM
Something like
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

Digita
06-30-2007, 03:09 AM
Hi Mdmackillop,

Yes. Your code is what exactly I'm after. Thanks so much for your help.

Have a nice w/end.

Regards

KP

Digita
07-01-2007, 09:31 PM
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