PDA

View Full Version : Solved: Whenever cell is added will be recorded on another sheet with date and time and value



marreco
08-01-2012, 04:56 PM
Hi
The cell 'E6' is the sum of the range 'E2: E8'

I wish every time that cell 'E6' suffer change, was made a historical data (value) in the 'second sheet' with the date and time.
Sheet2
A2------------B2
$123,23----01/08/2012 13:00
$135,62----01/08/2012 16:45
$235,15----02/08/2012 08:30
$345,23----02/08/2012 09:12

I tried this but without success
Private Sub Worksheet_Calculate()
If Range("E9").Calculate Then
Range("E9").Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
End If

End Sub

Bob Phillips
08-02-2012, 12:21 AM
Does E6 get updated directly, or is there a formula that updates it?

patel
08-02-2012, 12:33 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2:E8")) Is Nothing Then
firstemptyrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets(2).Cells(firstemptyrow, 1).Value = Range("E9").Value
Sheets(2).Cells(firstemptyrow, 2).Value = Date & Time
End If
End Sub

marreco
08-02-2012, 03:03 AM
Hi.
xld, formula is

patel, the code is not working

Thank you!!!

patel
08-02-2012, 04:05 AM
attach your file

Bob Phillips
08-02-2012, 04:10 AM
Try this

Private Sub Worksheet_Calculate()
Dim prev As Range

With Sheet2

Set prev = Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp)(2)
If prev.Offset(-1, 0).Value <> Me.Range("E6").Value Then

prev.Value = Me.Range("E6").Value
prev.Offset(0, 1).Value = Now
prev.Offset(0, 1).NumberFormat = "dd/mm/yyyy hh:mm"
End If
End With
End Sub

marreco
08-02-2012, 04:11 AM
See my attachment
Thank you!

patel
08-02-2012, 06:25 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:D8")) Is Nothing Then
firstemptyrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets(2).Cells(firstemptyrow, 1).Value = Range("E9").Value
Sheets(2).Cells(firstemptyrow, 2).Value = Date & Time
End If
End Sub

marreco
08-02-2012, 10:32 AM
XLD, your code worked perfectly!! Thank you very much!

patel, now your not sure what I did wrong, because it did not work!

Thank you both for helping me!

Thank you!