Prev: ODBC Driver Manager: Option type out of range
Next: Conditional Format help
From: Rebecca on 18 Mar 2010 06:35
Hi Everyone,
I am compiling a table/worksheet that collects date related numerical
information from another worksheet. ie:

The table collecting the info is just columns of dates for a year with
adjacent columns for data collection.

The worksheet supplying the info has new information entered every day under
that days date. (the date changes automaticaly & the numerical info is always
entered in the same cell adjacent to the date [a bit like a form]).

I have no problem collecting the info for todays date BUT!!! how can I stop
that info from changing (how can I save it) in the collecting worksheet, when
the date on the supplying worksheet moves on the the next day/date with new
info entered ?

Hope this makes sense to someone.
--
Thanks Becky
From: Gord Dibben on 18 Mar 2010 12:19
How about you save to another worksheet?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
.Offset(1, 0).Value = Target.Value
.Offset(1, 1).Value = Format(Date)
End With
End If
stoppit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the above code
into that module.

When you enter data in A1 it will be copied to Sheet2 column A and the date
entered in column B.


Gord Dibben MS Excel MVP

On Thu, 18 Mar 2010 03:35:01 -0700, Rebecca <jones(a)btinternet.com> wrote:

>Hi Everyone,
>I am compiling a table/worksheet that collects date related numerical
>information from another worksheet. ie:
>
>The table collecting the info is just columns of dates for a year with
>adjacent columns for data collection.
>
>The worksheet supplying the info has new information entered every day under
>that days date. (the date changes automaticaly & the numerical info is always
>entered in the same cell adjacent to the date [a bit like a form]).
>
>I have no problem collecting the info for todays date BUT!!! how can I stop
>that info from changing (how can I save it) in the collecting worksheet, when
>the date on the supplying worksheet moves on the the next day/date with new
>info entered ?
>
>Hope this makes sense to someone.

 | 
Pages: 1
Prev: ODBC Driver Manager: Option type out of range
Next: Conditional Format help