capture date and data every time data changes
Maybe this will get you started. The macro goes in the module for the
worksheet where the user is entering the data. If that's Sheet1, the module
name is also Sheet1. I've assumed it's to be copied to Sheet2.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Long
If Target.Address = "$C$1" Then
With Worksheets("Sheet2")
R = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(R, 1).Resize(1, 2).Value = Array(Date, Target.Value)
End With
End If
End Sub
On Sun, 12 Sep 2004 01:58:01 -0500, Myrna Larson
wrote:
Yes, but I need more information, specifically the layout of the sheet to
which the data (and the date) are to be transferred, e.g. to Sheet 2, next
available row, columns A and B...
On Sat, 11 Sep 2004 11:59:02 -0700, Myriam
wrote:
I agree with you 100%, but again, I have to work with what I have.
My idea was to write a macro that when the user hits enter, it would also
record the data of the cells in question and stamp date them on another
sheet. Once I have the data with a date, I can manipulate it any way I want.
Can this be done? Can you help me write this macro?
Thanks, I really appreciate it.
"Myrna Larson" wrote:
Sounds to me like you don't have the right layout. If the user over-writes
data in C1, you have NO audit trail in case there's an error. I think it
would
be better to enter all amounts AND dates, on another sheet. Then use
formulas
to transfer whatever is needed to the sheet you are talking about now.
On Fri, 10 Sep 2004 20:37:04 -0700, Myriam
wrote:
Hello,
I need your help again...! please!
I need to know what data a particular cell had at the end of each month.
These cells are not tied to a date so they cannot be filtered.
Can I somehow after the User enters new data in the cell and clicks Enter
automatically copy the figure to another Sheet under the corresponding
month?
e.g.,
On 9/10/04 User enters in Sheet1!C1 the amount of $20
on 10/10/04 user changes the same cell to $40
on 11/25/04 user changes the same cell to $60
I need to capture the amount, per month, on a separate sheet.
9/04 $20
10/04 $40
11/04 $60
Again, thanks to all for any help you can give me.
|