DISCOVER
×

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

Updated July 20, 2017

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.

ptMyPivotTable.RefreshTable

Release the resources you used in your PivotTable object by setting it equal to Nothing.

Set ptMyPivotTable = Nothing

Tip

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.

bibliography-icon icon for annotation tool Cite this Article

About the Author

Susan Hare began writing professionally in 2009. Many of her articles have been published on eHow. She writes on a varieity of topics including software, programming and home improvement. Hare is a professional consultant with a Bachelor of Science in computer science and software engineering from Rose-Hulman in Indiana.