Saving collected data?

[Rebecca]

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.

 

[Gord Dibben]

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