title: VBA Absolute Formula range changing when running a copy + Paste macro
body: <p>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.</p>
  
  <p>For example, my original VLOOKUP formula looks from <code>$D$2:$G$5000</code>, but when I run my macro it will change it to <code>$D$2:$G$1254</code> where 1254 is the last row where data resides.</p>
  
  <p>Here is the copy + paste function:</p>
  
  <pre><code>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" &amp; Rows.Count).End(xlUp).Row
  
  'Copies Current Week into Prior Week and deletes Rows in Prior week
  
  shCurrentWeek.Range("A4:X" &amp; lr).Copy
  shPriorWeek.Range("A2").PasteSpecial xlPasteValues
  shPriorWeek.Range("A" &amp; lr - 2 &amp; ":A10000").EntireRow.Delete
  
  End Sub
  </code></pre>
  
  <p>any ideas?</p>