Simple Copy and Update Cell Value Every Minute
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 7 123 ... LastLast
Results 1 to 10 of 62

Thread: Simple Copy and Update Cell Value Every Minute

  1. #1
    New Member
    Join Date
    Aug 2006
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Simple Copy and Update Cell Value Every Minute

     
    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!

  2. #2
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    6,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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.

  4. #4
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    6,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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.

  6. #6
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    6,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  7. #7
    New Member
    Join Date
    Oct 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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.

  8. #8
    New Member
    Join Date
    Oct 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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

  9. #9
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    6,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

    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.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  10. #10
    New Member
    Join Date
    Oct 2009
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Copy and Update Cell Value Every Minute

      
    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.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com