Record cell values at specific time or 'Now'

[albatross32]

If I have A1, B1 and C1 that contain constantly changing values I want to be able to record a snapshot of their values in, say, A2, B2 and C2 at either a given point in time or 'Now'

Thanks in anticipation.

 

[albatross32]

Come on chaps someone must have some ideas

 

[Tom Urtis]

Gee, with a name like albatross are you expecting people to flock to you?

OK, joke's over, please explain how the values get there...manually entered, by formula, or what.

 

[albatross32]

From an online source. I will not be able to control changes to A1, B1 etc

 

[Tom Urtis]

Well, odds are it is a change that would trigger a Change event if it is not from a link formula from that outside source.

So custom format cells A2:C2 as you wish.

Then, right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:C1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(1).Value = VBA.Now
End Sub

 

[albatross32]

This appears to just record the time when the row 1 cells change. I need row 2 to record actual values of row 1 as a snap shot.

 

[Tom Urtis]

Then I don't understand the crux of the question. Whenever you change a value in A1:C1 you want the value itself to also be present in the cell that was changed below it? For that you could just have a formula such as
=A1 or =B1 or =C1
which I am sure you know, but what would be the difference between that and this as (and please test to see what I mean)...

substituting the code line
Target.Offset(1).Value = VBA.Now

with
Target.Offset(1).Value = Target.Value


There must be something obvious I am missing, maybe you are doing a saveas after each change to preserve the snapshot objective. Even then if that's the case a formula would do. Don't understand, unless you just don't want formulas in the cells.

 

[albatross32]

If you think of A1:C1 as stock values that are always changing. All I want to be able to do is on a click of a button record in A2:C2 the values of A1:C1 at that instant. A2:C2 would then remain fixed as my 'snap shot' whilst leaving A1:C1 to continue with variable values.

Many thanks for your time to date. I am on the other side of the pond in the UK where it is now tomorrow and I need to turn in. I will review any further comments you may have later.

Thanks again

 

[Tom Urtis]

My code does what you said, so I think you need to expand on what you want to have happen after that instant of the cells in A1:C1 changing. For example, maybe you want to keep stacking the values on the next available row (A3:C3, A4:C4, and so on) or maybe you want to saveas the file. Otherwise, based only on your explanation, the A2:C2 values will keep overriding themselves, absent further direction from you as to what to do in between changes to A1:C1.

 

[pedro-egoli]

This is only a suggestion and I have ever only used the Date part to retain a firm date in spreadsheet, as Now function changes.
I use Ctrl+;

Did an experiment after a bit of searching and found that TIME can be set by Ctrland shift+:

So I experimented with Ctrl+; space space Ctrl/Shift+:

This bought up both date and time.

Format shown is Custom dd-mm-yy h:mm.

The Ctrl and Shift need to be selected together with the colon for Time, and just the Ctrl and semi-colon together for Date.

Give it a try and let us know tomorrow whether it worked.
The Date only option will for sure

Pedro