Insert Copy and Paste with a formula

[DBaker7777]

I have the following code that works great. I want to add a formula to column S to Sheet2 each time a row is pasted. The formula would need to increment each time it is pasted.
Something like
=IF(L1>Sheet1!$A$1,"NEW","") and the Next formula that gets placed in should be =IF(L2>Sheet1!$A$1,"NEW,"") and so on . Any help would be greatly appreciated.

Private Sub CommandButton2_Click()
Dim lastrow as long
lastrow=Sheets("Sheet2").Range("A655536").End(xlup).row+1
Activeworkbook.Sheets("Sheet1").Range("A1:R1").copy
Activeworkbook.Sheets("Sheet2").Range("A" & lastrow).pastespecial Paste=xlpastevalue
End Sub

 

[jsb1921]

Private Sub CommandButton2_Click()
    Dim lastrow As Long
    lastrow = Sheets("Sheet2").Range("A655536").End(xlUp).Row + 1
    ActiveWorkbook.Sheets("Sheet1").Range("A1:R1").Copy
    ActiveWorkbook.Sheets("Sheet2").Range("A" & lastrow).PasteSpecial Paste = xlpastevalue
   
    Dim str1 As String
' modify the next line according to your formula need changing lastrow if needed
    str1 = "=IF(L" + lastrow + ">Sheet1!$A$" + lastrow + ",""NEW"","""")"
    Sheets("Sheet2").Cells(lastrow, "S").Formula = str1

End Sub