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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.