Microsoft Excel is a spreadsheet application. One feature of this program is that you can use its worksheets to store data similarly to the way you would in a database and then reference it in dynamic graphical displays. There are two types of displays you can use: PivotTables and PivotCharts. PivotTables can be used to sort, filter and summarise your data. The only way to change the data source used by a PivotTable once it has been created through the wizard is to update it from VBA code in the background.
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
Connect your PivotTable object with your actual Pivot Table. Copy the code below.
Set ptMyPivotTable = ActiveSheet.PivotTables(1)
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)
Update the data actually displayed in your Pivot Table by using the VBA command below.
Release the resources you used in your PivotTable object by setting it equal to Nothing.
Set ptMyPivotTable = Nothing
You can have the data in your Pivot Table refresh every time the actual data changes. To do so just set your worksheet to calculate automatically and add the RefreshTable command to the Worksheet_Calculate function. See the link in the Resources section for more details.