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!

 

[Gilby]

Hello All,

I was able to get jbeaucaire's code to work just fine, and it's nice and clean code. My question is (and I think it was asked before but never answered):

How do you change the following code:
---------
Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
-----------

in order to copy values in Column A to Column B, then Column C, then Column D, etc. I'm sure the answer is fairly simple, I'm just not excellent at VBA syntax. Thank you.

Brian

 

[jbeaucaire]

Perhaps this:

Range("A" & Columns.Count).End(xlToLeft).Offset(, 1).Value = Range("A1").Value

 

[Gilby]

Hi jbeaucaire, that did not seem to work. I did not get any error message running the "Start Timer" button, but no data was copied from column A to column B. So if A1,A2,A3 = 1,2,3 resp. I want to paste this to B1,B2,B3, then to C1,C2,C3. I think using columns is the way to go but not sure what's not working. Any other thoughts? thank you.

 

[jbeaucaire]

Maybe this:

Columns(1).Copy Cells(1, Columns.Count).End(xlToLeft).Offset(,1)

 

[Gilby]

That's a Yahtzee! This code works. Thank you JB!

 

[maxa311]

Hello JB,
This code definately works. I have 2 laptopts. in one office 2007 and in another office xp is there. in office 2007 the code stops working after giving value from B2 to B64. and in office Xp it stops working after giving values from B2 to B256. i think we need to modify the code so that we can get this code work for 6 hours which is the stock market timing. thanks if u can check it and let me know what to do about it.

thanks

Sanjay Bhavsar

Hi, i had same problem, but it works now after small adjustment
Range("B" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value

And big thanks to JB for code and instructions!

 

[Niranjanmeyda]

Hi
I have real time data in columns A,B,C
and i want to copy them to another three columns D,E,F in 5 second time interval
than again copy A,B,C to another three columns G,H,I
in 5 second time interval
i want do that until last three columns.

 

[Chan Yi Wei]

Hi guys, i tried to set the recording interval to 1minutes

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

 

[Chan Yi Wei]

But i failed to do it. Can only run the recording at 59s

TimeValue("00:00:59")

 

[amitsan]

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.

Dear jbeaucaire,

Thank you very much....you are a life saver...i was searching this type of code for many days...really thank you very much...