Simple Copy and Update Cell Value Every Minute

user04

New Member
Joined
Aug 1, 2006
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

New Member
Joined
Oct 23, 2009
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

New Member
Joined
Oct 23, 2009
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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
Rich (BB 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:
Rich (BB 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
Rich (BB 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

New Member
Joined
Oct 23, 2009
Messages
6
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

New Member
Joined
Oct 23, 2009
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

New Member
Joined
Oct 23, 2009
Messages
6
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.
 

Forum statistics

Threads
1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top