using a timer to automate a macro

Hi,

I cant figure out how to get the data to be pasted in the cell below on each new event of the timer. below is teh code i'm using

Sub ButtonCode()
     Dim rows As Integer
     ' execute macros
    rows = rows + 1
    RefreshData
    

    Sheets("FTSE 100").Select
    Range("B10:J10").Select (copied data will always have the same location)
    Selection.Copy
    Sheets("sheet2").Select
    Range("B" & rows).Select (want to increase the row number by one for each new set of data)   
 Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("G18").Select
    
   
     ' submit macro to run again in 5 min
    Application.OnTime Now + TimeValue("00:01:00"), "ButtonCode"
rows = rows

End Sub

The code updates as it should but overwrites the same cells each time.

Any help much appreciated

The rows variable will be zero'd every time the routine runs.

Use code to get next empty row.

rows = activesheet.cells(activesheet.rows.count,1).end(xlup).row+1