I am running a copy paste macro but when I run it, it keeps changing the range of my formula to the last of row.

For example, my original VLOOKUP formula looks from `$D$2:$G$5000`, but when I run my macro it will change it to `$D$2:$G$1254` where 1254 is the last row where data resides.

Here is the copy + paste function:

```
Sub START1()

Dim shCurrentWeek As Worksheet
Dim shPriorWeek As Worksheet
Dim lr As Long

Set shCurrentWeek = ActiveWorkbook.Sheets("Current Week")
Set shPriorWeek = ActiveWorkbook.Sheets("Prior Week")
lr = shCurrentWeek.Range("A" & Rows.Count).End(xlUp).Row

'Copies Current Week into Prior Week and deletes Rows in Prior week

shCurrentWeek.Range("A4:X" & lr).Copy
shPriorWeek.Range("A2").PasteSpecial xlPasteValues
shPriorWeek.Range("A" & lr - 2 & ":A10000").EntireRow.Delete

End Sub

```

any ideas?