Not sure about some of the logic, but I think being 100% explicit when specifying the worksheet names will help


Option Explicit

Private Sub CommandButton1_Click()
Dim i As Long
Dim endRow As Long
Dim LastRow As Long
Dim wsRecoveries As Worksheet
Dim wsEarn As Worksheet

Set wsRecoveries = Worksheets("Sheet2")
Set wsEarn = Worksheets("Sheet1")

LastRow = wsRecoveries.Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To LastRow
    'Calulate Office recovery Total
    wsRecoveries.Range("R" & i) = WorksheetFunction.Sum(wsRecoveries.Range("C" & i & ":Q" & i))
    'Calulate Outside recovery Total
    wsRecoveries.Range("AG" & i) = WorksheetFunction.Sum(wsRecoveries.Range("S" & i & ":AF" & i))
    
    'Calulate Total recovery Total
    wsRecoveries.Range("AH" & i).Value = wsRecoveries.Cells(i, 18) + wsRecoveries.Cells(i, 33)
Next i

'Calculate Net pay
endRow = wsEarn.Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To endRow
    wsEarn.Range("AI" & i).Value = wsRecoveries.Cells(i, 16) - wsEarn.Cells(i, 34)
Next i
End Sub