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

  • #16
    when i clicked debug it highlighted some code

    Click image for larger version

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

    Comment


    • #17
      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..........

      Comment


      • #18
        Hello,

        Before getting into more modifications ... can you confirm your ' static - dynamic ' issue is solved thanks to the second version of the Data macro ...???
        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


        • #19
          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.

          Comment


          • #20
            Hello,

            Sorry for my typo ...

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



            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


            • #21
              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
              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


              • #22


                Hello,

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

                Hope this will help
                Attached Files
                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

                Working...
                X