title: VBA Updating Source Data for Pivot Table to end of row
body: <p>I am trying to figure out how to update my pivot table source data to the end of row when the data changes using VBA. My current code is below:</p>
  
  <pre><code>Dim shBrandPivot As Worksheet
  Dim shCurrentWeek As Worksheet
  Dim shPriorWeek As Worksheet
  Dim shPivot As Worksheet
  Dim lr As Long
  
  
  Set shBrandPivot = ActiveWorkbook.Sheets("Brand Pivot")
  Set shCurrentWeek = ActiveWorkbook.Sheets("Current Week")
  Set shPriorWeek = ActiveWorkbook.Sheets("Prior Week")
  Set shPivot = ActiveWorkbook.Sheets("Pivot")
  lr = shCurrentWeek.Range("A" &amp; Rows.Count).End(xlUp).Row
  
  With ActiveWorkbook.Sheets("Pivot").Activate
  
  ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="CurrentWeek!A3:X &amp; lr"
  
  End With
  </code></pre>
  
  <p>The error I am getting is Run time error 1004: Cannot open PivotTable source file: E:\offline\KXM2103\Data\CurrentWeek</p>