Automatically transferring data from one row to another

So either you can drag down the formula to maybe 100 rows at the outset (since its a small formula) or you could use this code -

Option Explicit

Sub update_data()
Dim lrow As Long

With Worksheets("Sheet1")
    lrow = .UsedRange.Rows.Count
    .Rows("2:2").Copy
    .Range("A" & lrow + 1).PasteSpecial (xlPasteValues)
    .Range("A2:R2").ClearContents
    .Range("S" & lrow).Formula = .Range("S" & lrow - 1).Formula
End With

End Sub