VBA Updating Source Data for Pivot Table to end of row

Question

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:

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" & Rows.Count).End(xlUp).Row

With ActiveWorkbook.Sheets("Pivot").Activate

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="CurrentWeek!A3:X & lr"

End With

The error I am getting is Run time error 1004: Cannot open PivotTable source file: E:\offline\KXM2103\Data\CurrentWeek

Answer 1

To do it exclusively in VBA you can try this.

Dim shBrandPivot As Worksheet
Dim shCurrentWeek As Worksheet
Dim shPriorWeek As Worksheet
Dim shPivot As Worksheet
Dim lr As Long
dim rng as range

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" & Rows.Count).End(xlUp).Row
set rng = shcurrentweek.range("A3:X" & lr)

With shPivot.PivotTables(1).PivotCache 
        .SourceData = rng.Address(True, True, xlR1C1, True)
        .Refresh
End With