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.

  • Carim
    replied
    Hello,

    Took a couple of minutes to finish up your Test file for tracking live data ...

    Hope this will help
    Attached Files

    Leave a comment:


  • Carim
    replied
    Hello,

    There are a few of modifications required to handle your latest request:

    1. Redesign the Movedata() macro

    2. In cell B10 add formula
    Code:
    =Time(Row()-5,0,0)
    ... and copy it down till cell B21

    3. Design the Workbook_Open() macro to handle your start and end times ...

    4. Adjust the formula in cell R22 to get the actual Average

    Leave a comment:


  • Carim
    replied
    Hello,

    Sorry for my typo ...

    Quite pleased to hear your macro is now 95% operational ...



    Leave a comment:


  • LRSD781
    replied
    Hello Carim,
    Yes the "Dynamic" data works great.
    The run-time error was solved by changing
    Code:
    Range("C10" & last).PasteSpecial xlPasteValues
    to
    Code:
    Range("C10").PasteSpecial xlPasteValues
    I tested it for quite some time without issues,

    Is there a way to allow it to restart, clear contents of column C and then begin pasting at a certain time?
    I will be running this 24/7 and will typically need it to restart at 4:30 AM.

    Leave a comment:


  • Carim
    replied
    Hello,

    Before getting into more modifications ... can you confirm your ' static - dynamic ' issue is solved thanks to the second version of the Data macro ...???

    Leave a comment:


  • LRSD781
    replied
    I need help editing the code below.

    I need it to re-start the copy/paste function at 4:00 AM every day.

    Excel will be running 24/7 (dashboard)


    Code:
     Dim TimeToRun
    
    Sub auto_open()
        Call Movedata
    End Sub
    
    Sub Movedata()
        TimeToRun = Now + TimeValue("00:00:10")
        Application.OnTime TimeToRun, "Data"
    End Sub
    
    Sub Data()
         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:C34").ClearContents
          Range("A10").Copy
          Range("C10").PasteSpecial xlPasteValues
        End If
        Application.CutCopyMode = xlCut
        Range("A10").Select
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Call Movedata
    End Sub
    
    Sub auto_close()
        On Error Resume Next
        Application.OnTime TimeToRun, "Data", , False
    End Sub
    I had to edit post as the code was horizontal and difficult to read..........

    Leave a comment:


  • LRSD781
    replied
    when i clicked debug it highlighted some code

    Click image for larger version

Name:	error.jpg
Views:	0
Size:	28.2 KB
ID:	1208085

    Leave a comment:


  • LRSD781
    replied
    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:	0
Size:	85.1 KB
ID:	1208083

    Leave a comment:


  • Carim
    replied
    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

    Leave a comment:


  • LRSD781
    replied
    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..

    Leave a comment:


  • Carim
    replied
    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 ...

    Leave a comment:


  • LRSD781
    replied
    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.

    Leave a comment:


  • Carim
    replied
    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 ...

    Leave a comment:


  • LRSD781
    replied
    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

    Leave a comment:

Working...
X