VBA deletes a column and adds a new column, how to refer to the same range area in a formula after these changes have been made?

Question

Image: Formula and range area

So I want my formula to count the range $E$27:$S$42 but anytime the macro is run the range referred to in the formula reduces by one column. How can I keep the range $E$27:$S$42 after the macro makes changes?

(The Macro Deletes an old week and adds a new future week by copying column R). The VBA is a for loop to make these changes to 20 blocks similar to the one as shown.

Code:

Sub DeleteAndAddWeek()


Dim i As Integer
For i = 22 To 617 Step 22
DoEvents

'Select Sheet
Sheets("Entry Sheet").Select

'Delete Old Date in Column E
Range(Cells(i + 1, 5), Cells(i + 20, 5)).Delete Shift:=xlToLeft

'Copy column R to Column S (new Week)
Range(Cells(i + 1, 18), Cells(i + 20, 18)).AutoFill Destination:=Range(Cells(i + 1, 18), Cells(i + 20, 19)), Type:=xlFillDefault
Range(Cells(i + 5, 19), Cells(i + 20, 19)).ClearContents

'Copy dates into Overview Sheet
Range(Cells(23, 5), Cells(23, 19)).Copy
Sheets("Team Forecast Overview").Select
Range("D2:R2").PasteSpecial xlPasteValues

'Copy summary rows into Overview Sheet
Sheets("Entry Sheet").Select
Range(Cells(i + 2, 5), Cells(i + 2, 19)).Copy
Sheets("Team Forecast Overview").Select
Range(Cells(((i / 22) + 2), 4), Cells(((i / 22) + 2), 18)).PasteSpecial xlPasteValuesAndNumberFormats


Next i


End Sub