capture data in Excel

  • Thread starter Thread starter gman26
  • Start date Start date
G

gman26

I would like to 'capture' the changing data in cell A1 and store it in
another worksheet or workbook. I have tried the IF statement, however,
it does not work right. I am trying to store production data 3 times a
day, 5 days a week, from one workbook to another.
Any help would be greatly appreciated.
 
You're gonna have trouble with formulas. They can return values to the cell
that has the formula, but they can't plop in the value into another cell. But
you could run a macro on demand that puts the value into a cell on the second
sheet:

Option Explicit
Sub testme01()

Dim FromCell As Range
Dim NextCell As Range

Dim fromWks As Worksheet
Dim toWks As Worksheet

Set fromWks = Worksheets("sheet1")
Set toWks = Worksheets("sheet2")

Set FromCell = fromWks.Range("A1")

With toWks
Set NextCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

NextCell.Value = FromCell.Value

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Hi,

Try this method, it may suit.

Add a sheet to the Workbook and name it "Tracker". In A1 of this sheet
add the heading "Data", in B1 add the heading "Time and Date".

Now select the sheet that has the changing A1 cell you need tracked.
Right click on the sheet name tab, select "View Code" and add the code
below.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
With Sheets("Tracker").Range("A65536").End(xlUp)
.Cells(2, 1) = Target
.Cells(2, 2) = Now
.Range("A1:B1").EntireColumn.AutoFit
End With
End If
End Sub

Click the top right X to get back to Excel and Save. Now each time A1
changes, the new value will be stored in Column A of the sheet Tracker
and the time and date in Column B.

Hope this helps.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
I tried the suggestion from Dave Hawley, however, it does not work
Perhaps I am typing something incorrectly
 
Dave Hawley's suggestion was an event macro.

Make sure you put it in the correct spot.

Right click on the worksheet tab that needs this behavior and select view code
and paste it in the code window.

If that's not what "it does not work" mean, post back with a few more specifics.
 
I used this and it works, however, this creates a new row of data eac
time the value changes. The explanation below shows more detail of wha
I am trying to accomplish.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
With Sheets("Tracker").Range("A65536").End(xlUp)
.Cells(2, 1).Value = Target.Value
.Cells(2, 2).Value = Now
.Range("A1:B1").EntireColumn.AutoFit
End With
End If
End Sub
When the value of A9=1 and the time of day is between 8am and 3:30pm
the value in A1 is stored in worksheet MONDAY at B1. When the value o
A9=1 and the time of day is between 4:00pm and 11:59pm, the value in A
is stored in worksheet MONDAY at C1. When the value of A9=1 and th
time of day is between 12am and 7am, the value in A1 is stored i
worksheet MONDAY at D1. This is done each day Monday through Friday s
I have data for the whole week. Ideally I would like to create
workbook for each week so I can go back to previous production data.
hope this helps. Thanks,
Georg
 
Hi

The below works fine when changing cells manually (thanks)
However the sheet I am using has the data in the changing cell feed from a source automatically, which changes the cell. Can you advise what I may need to do to allow for this cell changing dynamically, and allow the 'capture' spread sheet to print the changing cell value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
With Sheets("Tracker").Range("A65536").End(xlUp)
.Cells(2, 1) = Target
.Cells(2, 2) = Now
.Range("A1:B1").EntireColumn.AutoFit
End With
End If
End Sub
 
Back
Top