Announcement

Collapse
No announcement yet.

Want to automatically copy data from A1 to B2-B13 each hour then restart each day.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Want to automatically copy data from A1 to B2-B13 each hour then restart each day.



    Hello Everyone!
    I have a workbook and need to automatically copy "Live data" from A1 and paste that data into B2 at 5:00 AM,
    then, at the next hour (6:00 AM) paste the same updated data into B3 and so on..
    stopping at B13, at 4:00 PM.
    Then, each day at 5:00 AM clear the contents of B2-B13 and start again.
    This workbook will be running 24/7.

    Getting the Live data into A1 is not an issue for me.

    Anyone's help would be very much appreciated.

  • #2
    Hello,

    You will need to use Application.OnTime ...

    See video

    https://www.youtube.com/watch?v=LZUKnHva_Tw

    or example

    http://excelexperts.com/VBA-Tips-Run...nute-or-Second

    Hope this will help

    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Carim,
      Thank you so much for helping!
      Your reply was part of what I am in search for...Probably the first step.
      I viewed the tutorial and used the code, but my edit doesn't seem to work.

      in my code i want to copy the value (in this case the number 12) from B10 and paste into C10
      However the value in B10 clears from B10 (deletes) instead of paste into C10

      copy_paste_test.xlsm
      Attached Files

      Comment


      • #4
        Hello,

        In order to copy, you need to use following:

        Code:
        Range("B10").Copy Destination:=Range("C10")
        Hope this will help
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Carim,
          That did the trick!!
          The value pastes into C10 perfectly.
          on the next "time cycle" (in this case 3 seconds) I want to paste the value from B10 to C11.
          However, I want the previously pasted value in C10 to remain the same at time of paste (i.e. NOT updated)

          I need to do this consecutively C10-C21

          When C21 is "filled" I need to stop the macro...

          Thanks in advance

          Comment


          • #6
            Hello again,

            From your initial, my understanding was your target range was C10:C31 ... but for C21 ... you can use the following

            Code:
            Sub Data()
                Dim last As Long
                last = Application.WorksheetFunction.Max(10, Cells(Application.Rows.Count, "C").End(xlUp).Row + 1)
                Calculate
                If last <= 21 Then
                  Range("B10").Copy Destination:=Range("C" & last)
                Else
                  Range("C10:C31").ClearContents
                  Range("B10").Copy Destination:=Range("C10")
                End If
                Call Movedata
            End Sub
            Hope this will help
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              Thanks again Carim!

              Ok, Your code works perfect.
              I made a few changes...
              in "sub data" i changed the ClearContents value to cell C22 (my last data entry on the column would be 21)
              I also changed the copy from cell to A10 (I needed to use column B for time vales in a chart)
              see image below
              :Click image for larger version  Name:	copy and paste.jpg Views:	1 Size:	42.8 KB ID:	1208067

              Everything works great thus far...
              one issue is that my "source data" in A10 is "Live Data" that updates periodically.
              The copy/paste method works. However, all the values in column C update as well.

              I need the paste to be a sort of "one-shot" (paste to cell without updating) then paste the current Live data into the cell below.

              I hope I'm explaining this well....

              copy_paste_test.xlsm
              Attached Files

              Comment


              • #8
                Glad to hear your Data macro is almost fully operational ..

                Sorry ... but your latest request is not very clear to me ...

                As cell A10 gets updated by your live feed ... data is always copied to the first empty cell available in Column C ...
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment


                • #9
                  Thanks again Carim,
                  After reading my post I see where I should have made it more understandable, my apologies.

                  Since the data in A10 is "Dynamic" and I'll be tracking or trending that dynamic data in a chart over a period if time,
                  I need the Data that is pasted into the cells (column C) to be "Static".

                  sort of a one-shot "copy/paste/freeze"

                  Trending Live Data in a chart....
                  "I should have said this to begin with"

                  Then after that get resolved,
                  I need a macro to clear column C at 4:45 AM then re-start the copy/paste macro at 5:00 AM everyday
                  that would be awesome!
                  Excel will be running 24/7

                  Comment


                  • #10
                    Hello again,

                    I need the Data that is pasted into the cells (column C) to be "Static"
                    From my perspective ... that is already the case ...

                    Each time A10 changes ... every x seconds ... the value gets copied and NOT the formula ...
                    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                    Comment


                    • #11
                      Carim,
                      Thank you for being so patient with me.

                      When I feed my "Live Data" into A10 (this data changes once an hour) so my code for triggering the copy/paste will
                      change to 01:00:00. ( have it set now to 10 seconds for testing)
                      Each time the "paste" executes and moves the data to a cell the "pasted or moved" data is remaining "Dynamic".
                      What I'd like to do is take the dynamic data and paste it as static data once an hour.

                      Comment


                      • #12
                        OK ...

                        Let's play a simulation game ...

                        You will have to be the " Live Update" ...

                        Position your cursor in cell A10 and type in any number

                        Wait for 10 seconds ... for your macro to run ... and to update your charts ...

                        Then type in a different number and wait again for the macro to run ...

                        Let me know if the ' static / dynamic ' problem is solved ... or not ...
                        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                        Comment


                        • #13
                          Carim,
                          Your correct, If I enter the data into A10 manually it pastes that data into the cells in column C.
                          even when I change that data "manually" in A10 the changed value also gets pasted into column C.

                          However, When I "paste special unicode text then select the paste link radio button" my link goes active
                          as a dynamic value in A10.
                          The Value in A10 changes values constantly, then as the macro copies and pastes that value moves to column C
                          and dynamically changes with A10.

                          So the code does in fact work ONLY with static numbers..

                          Comment


                          • #14
                            However, When I "paste special unicode text then select the paste link radio button" my link goes active
                            as a dynamic value in A10.
                            Quite honestly ... I do not understand what you are saying ...

                            Have a test with the following macro :

                            Code:
                            Sub Data2()
                                Dim last As Long
                                Application.Calculation = xlCalculationManual
                                Application.ScreenUpdating = False
                                last = Application.WorksheetFunction.Max(10, Cells(Application.Rows.Count, "C").End(xlUp).Row + 1)
                                If last <= 21 Then
                                  Range("A10").Copy
                                  Range("C" & last).PasteSpecial xlPasteValues
                                Else
                                  Range("C10:C22").ClearContents
                                  Range("A10").Copy
                                  Range("C10" & last).PasteSpecial xlPasteValues
                                End If
                                Application.CutCopyMode = xlCut
                                Range("A10").Select
                                Application.ScreenUpdating = True
                                Application.Calculation = xlCalculationAutomatic
                                Call Movedata
                            End Sub
                            HTH
                            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                            Comment


                            • #15


                              Carim,
                              It worked great until the paste occurred in C21 or maybe when the clear contents occurred in C22.
                              gave me a run time error.

                              here is a screenshot:
                              Click image for larger version

Name:	run_error.jpg
Views:	1
Size:	85.1 KB
ID:	1208083

                              Comment

                              Working...
                              X