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.

Hi
though this can be done using an event procedure (using the
worksheet_change ecvent) I don't recommend using it. Why not let the
user enter the date AND the amount

--
Regards
Frank Kabel
Frankfurt, Germany

"Myriam" schrieb im Newsbeitrag
...
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.

Why don't you recommend the worksheet_change event?

(I am working with a workbook developed by someone else and
adding date on those particular cells, at this point, is not an option)

"Frank Kabel" wrote:

Hi
though this can be done using an event procedure (using the
worksheet_change ecvent) I don't recommend using it. Why not let the
user enter the date AND the amount

--
Regards
Frank Kabel
Frankfurt, Germany

"Myriam" schrieb im Newsbeitrag
...
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.

It sounds to me as though the workbook should be redesigned, along the lines
of what I mentioned in my previous reply.

On Sat, 11 Sep 2004 09:15:07 -0700, Myriam
wrote:

Why don't you recommend the worksheet_change event?

(I am working with a workbook developed by someone else and
adding date on those particular cells, at this point, is not an option)

"Frank Kabel" wrote:

Hi
though this can be done using an event procedure (using the
worksheet_change ecvent) I don't recommend using it. Why not let the
user enter the date AND the amount

--
Regards
Frank Kabel
Frankfurt, Germany

"Myriam" schrieb im Newsbeitrag
...
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.

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.

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.

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.

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.

Here is some code that captures only the last entry for a given month. If the
year+month of the last pre-existing entry is the same as the current
year+month, that last data point will be overwritten.

Otherwise, the last pre-existing data point will be kept. That will happen
even if it's *later* than the current date. (The latter would happen only if
the computer clock had been set incorrectly in the past, or someone has
altered the data.)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PrevEntry As Variant
Dim R As Long

If Target.Address = "$C$1" Then
With Worksheets("Sheet2")
R = .Cells(.Rows.Count, 1).End(xlUp).Row
PrevEntry = .Cells(R, 1).Value2

If IsEmpty(PrevEntry) Then
'use this row

ElseIf IsNumeric(PrevEntry) Then
If Format$(Date, "yyyymm") < Format$(PrevEntry, "yyyymm") Then
R = R + 1
End If

Else 'text, boolean, error?
R = R + 1

End If

.Cells(R, 1).Resize(1, 2).Value = Array(Date, Target.Value)

End With
End If
End Sub



On Sun, 12 Sep 2004 02:08:33 -0500, Myrna Larson
wrote:

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.