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

Written by susan hare
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Change the Source of an Excel Pivot Table Using VBA
PivotTables can display sub-totals or averages of your data. (number background image by kuhar from

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.

Skill level:
Moderately Easy


  1. 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. 2

    Connect your PivotTable object with your actual Pivot Table. Copy the code below.

    Set ptMyPivotTable = ActiveSheet.PivotTables(1)

  3. 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. 4

    Update the data actually displayed in your Pivot Table by using the VBA command below.


  5. 5

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

    Set ptMyPivotTable = Nothing

Tips and warnings

  • 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.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.