Simple Copy and Update Cell Value Every Minute

[user04]

I am trying to grab the value of a cell every time it updates and copy that value and store in a new cell.

So I have a cell (A2) and it updates every 60 seconds, I need that value for t=1 (first time) to be copy and pasted into a new cell (B2). Then when t=2 (second time, 60 seconds later) I need the value to be pasted into the same column but a new row, namely cell C2. Then when t=3 (third time), I need it to be copied to cell D2 and so on.

I'm assuming it just a macro that is called every 60 seconds and grabs the value of A2, but then needs to find the next cell in column B that is empty and paste it there. Is this right?

I've tried to look up for an hour now on how to do something like this but all the VB code I see doesn't really help me out. I'm assuming this should be a relatively simple operation, but just can't seem to find any help on it.

Any help is greatly appreciated!

Thanks!

 

[ea123]

How to use the macros:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code

Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
    Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    Call StartTimer
End Sub


Sub StartTimer()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

5. Get out of VBA (Press Alt+Q)
6. Save your sheet
7. Click on the Control Toolbox Command Button icon
8. Draw a button on your worksheet
9. Right-click the button and select Properties
10. Change the Caption to Start Timer
11. Right-click the button and select View Code
12. Paste in this code for the commad button:

Private Sub CommandButton1_Click()
    Call StartTimer
End Sub

13. Switch back to your spreadsheet
14. Repeat steps 7-12 to create a Stop Timer button

Private Sub CommandButton2_Click()
    Call StopTimer
End Sub

15. Press Alt-Q to close the VBEditor and save your sheet

Try the new buttons.

This works like a charm. But it stops updating the last row once all the rows in sight are filled- example it will stop updating row # 64 and start again at row # 1. Any idea how to go around this? I have about 360 rows which I need to be auto updated every minute . Thank you so much!

 

[xyloman306]

How to use the macros:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code

Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
    Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    Call StartTimer
End Sub


Sub StartTimer()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

5. Get out of VBA (Press Alt+Q)
6. Save your sheet
7. Click on the Control Toolbox Command Button icon
8. Draw a button on your worksheet
9. Right-click the button and select Properties
10. Change the Caption to Start Timer
11. Right-click the button and select View Code
12. Paste in this code for the commad button:

Private Sub CommandButton1_Click()
    Call StartTimer
End Sub

13. Switch back to your spreadsheet
14. Repeat steps 7-12 to create a Stop Timer button

Private Sub CommandButton2_Click()
    Call StopTimer
End Sub

15. Press Alt-Q to close the VBEditor and save your sheet

Try the new buttons.

Thank you so much @jbeaucaire , the code works perfect. Just registered here to say "Thank you, you are truly a life saver ".

 

[Sunrise17]

Why copy a value every 5 seconds? Is it being changed via formula?

Paste all of this into a regular module:

Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
    Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    Call StartTimer
End Sub


Sub StartTimer()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

Then put a couple of Control Box Toolbar command buttons on your sheet, make the caption on the first one "START TIMER" and the caption on the second "STOP TIMER". Then doubleclick them and put this code into those buttons:

Private Sub CommandButton1_Click()
    Call StartTimer
End Sub

Private Sub CommandButton2_Click()
    Call StopTimer
End Sub

Now you can easily turn this timer off and on at will.

Hi JB,

Can you please help me in putting actual time rather than now - dTime = Now + TimeValue("00:00:05") here. Because I want to schedule something and also it will be great if you can help me with end time.

Regards,
Sun