Simple Copy and Update Cell Value Every Minute

**user04** · Mar 20th, 2009, 07:08 PM

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!

**jbeaucaire** · Mar 20th, 2009, 07:35 PM

Rightclick on the sheettab and select VIEW CODE. Paste in this macro:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    End If
End Sub

**fcc05** · Oct 23rd, 2009, 02:43 PM

HeY JB!
This is a person who really needed this vb code and had same question which is resolved by you. I was searching this thing since last 4 onths because i m not a software engineer. Thank you very much for helpinng an unknown person to u. I need little more help. Is it possible that the cell copies value every five seconds in the same manner its copying when value changes? I would rellay appreciate it if i get the solution to it.

**jbeaucaire** · Oct 23rd, 2009, 06:42 PM

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

Paste all of this into a regular module:

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

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:

Code:

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.

**fcc05** · Oct 26th, 2009, 05:24 AM

Hey JB!
Thanks for replying so soon. I have more requests for you if u can help me out.

Regarding the first code,

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
End If
End Sub

above code works perfact and it starts changing values in b2, then b3, then b4........bn, when we change the value in A1 manually. but when the value of A1 changes autometically with formula, the code doesn't work. Actually I m a stock market guy and its my full time business. i import live data of stock market in to excel and then use formulas among the live data to obtain specific results i required. if you can resolve this issue i can store intra day data of some stocks which can help in my research work as well as help me in day trading. Please help me regarding this matter. i can send u file if u want me to to explain you what exactly is my requiremet, if u wish.

Regarding The code you sent me for value changes every 5 seconds, JB, I do not know vb and any other softwares, so i do not know how to copy and paste this code and how to activate it. I tries to copy the code the way i did in previous code, but it did not work. actually i do not do how to deal with such things, i will be really obliged if u can explain me in more detail. I would really appreciate if u can do these two things for me. Thanks.
regards,

Sanjay Bhavsar.

**jbeaucaire** · Oct 26th, 2009, 07:47 AM

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

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:

Code:

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

Code:

Private Sub CommandButton2_Click()
    Call StopTimer
End Sub

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

Try the new buttons.

**fcc05** · Oct 27th, 2009, 06:54 AM

Dear JB! Its working great. I totally appreciate your time and help for me here. As i m not a software designer, i find these things little new, but after following the steps you wrote here, i could make it. Now i want to try in A2, A3 .. A200, and the values of the cells of columun A will be recorded in the columns B, C, D,..... respectively. so that i can add all my stock scripts column A and will get the intra day records of all the scripts in respective columns. If u can show me a sampel to do that, it would be a great help. I know i am troubling you a lot everyday, eventhough i do not know you. But u seem a kind person. I would be happy if i would get the solution. thank you very much JB.

Regards,

Sanjay Bhavsar.

**fcc05** · Oct 27th, 2009, 08:46 PM

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

**jbeaucaire** · Oct 27th, 2009, 08:57 PM

Something else must be interfering. There's nothing in the code that's limited by time. It restarts the timer each time it enters a value...adds 5 seconds and counts again.

I don't use Excel 2007, so can't help troubleshoot that...but again, there's nothing in the code that shouldn't work the same in both.

Perhaps someone with Excel 2007 can offer some suggestions, the approach I've shown is a standard one.

**fcc05** · Oct 27th, 2009, 10:22 PM

Thanks for your prompt reply JB. I will try to find out whats going wrong in my computer. I request you too llok in the matter when u get chance. I have one more request. can u write me a code which gives sound alert when the changing value of a cell reaches or crosses some specific value. for example i want a sound alert when value in A1 reaches to 275.
thanks
Sanjay Bhavsar.