How to sum and Copy paste in sheet 2 row incrementally

Question

I am new to Excel VBA. With reference from a search engine I have created the above code but stuck at one point. My requirement is to sum data in the range of sheet1 (A2:A9) and copy the output of sum to sheet2 ("A1").

I am planning to schedule the code to run for every 5 minute.For the first 5 minute run the output has to be stored in sheet2("A1") and for the 10th minute run output to be stored in sheet2("A2") and for the 15th minute output has to stored in sheet2("A3"). Like this the output has to be stored incrementally.

Below is my code:

Sub Hello()
Dim myData As DataObject
Set myData = New DataObject
Dim a As String
Worksheets("Sheet1").Range("A2:A9").Select
a = WorksheetFunction.Sum(Selection)
myData.SetText a
myData.PutInClipboard
lastrow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Cells(lastrow + 1, 1).Select
myData.GetFromClipboard
Worksheets("Sheet1").Range("A2").Select
Call Schedule
End Sub

Sub Schedule()
Application.OnTime Now + TimeValue("00:05:00"), "Hello"
End Sub

Answer 1

The code below would run every 5 minutes for 1 hour; to have it run for, e.g 2 hours instead, you would have to increase the upper limit of the i value in Schedule() (from 60) to 120 etc.

Public runCount As Long
Sub Hello()
    With Worksheets("Sheet2")
        runCount = runCount + 1
        .Cells(runCount, 1).Value = WorksheetFunction.Sum(Worksheets("Sheet1").Range("A2:A9"))
        .Cells(runCount, 2).Value = (runCount * 5) & "th minute"
    End With
End Sub
Sub Schedule()
    Dim i As Long
    For i = 5 To 60 step 5
        Application.OnTime Now + TimeValue(INT(i/60)&":"&(i Mod 60)), "Hello"
    Next i
End Sub