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

[marreco]

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
[VBA]Private Sub Worksheet_Calculate()
If Range("E9").Calculate Then
Range("E9").Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
End If

End Sub[/VBA]

[Bob Phillips]

Does E6 get updated directly, or is there a formula that updates it?

[patel]

[VBA]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[/VBA]

[marreco]

Hi.
xld, formula is

patel, the code is not working

Thank you!!!

[patel]

attach your file

[Bob Phillips]

Try this

[VBA]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[/VBA]

[marreco]

See my attachment
Thank you!

[patel]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C28")) 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[/VBA]

[marreco]

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!