How to Change the Source of an Excel Pivot Table Using VBA

1. Create a PivotTable object in VBA so that you can update the source. Copy the code below and put it in your macro or function.Dim ptMyPivotTable As PivotTable
2. Connect your PivotTable object with your actual Pivot Table. Copy the code below.Set ptMyPivotTable = ActiveSheet.PivotTables(1)
3. Update the SourceData property of your PivotTable object to the new values. You can do this by copying the VBA code provided. Substitute your desired data range in place of 'A1' in the example.ptMyPivotTable.SourceData = Range('A1').CurrentRegion.Address(True, True, xlR1C1, True)To reference a range on a different sheet, you can use the following code.ptMyPivotTable.SourceData = Sheets('mySheetName').[A1].CurrentRegion.Address(True, True, xlR1C1, True)
4. Update the data actually displayed in your Pivot Table by using the VBA command below.ptMyPivotTable.RefreshTable
5. Release the resources you used in your PivotTable object by setting it equal to Nothing.Set ptMyPivotTable = Nothing