How to update a PivotTable when source data changes using VBA?

To update a PivotTable in Excel when the source data changes using VBA, you can write a macro that refreshes the PivotTable. Here is a step-by-step guide on how to do this:

   Sub RefreshPivotTable()
       Dim ws As Worksheet
       Dim pt As PivotTable
       
       ' Change "Sheet1" to the name of your sheet containing the PivotTable.
       Set ws = ThisWorkbook.Sheets("Sheet1")
       
       ' If there's only one PivotTable on the worksheet, you can refer to it by its index [1].
       ' Otherwise, use the name of the PivotTable: ws.PivotTables("PivotTableName").
       Set pt = ws.PivotTables(1)
       
       ' Refresh the PivotTable.
       pt.RefreshTable
       
       ' Alternatively, to refresh all PivotTables on the worksheet, use the following:
       ' For Each pt In ws.PivotTables
       '     pt.RefreshTable
       ' Next pt
       
       MsgBox "PivotTable refreshed successfully!"
   End Sub

Open the VBA Editor: Press `ALT + F11` in Excel to open the Visual Basic for Applications editor.
Insert a Module: Right-click on any of the items in the Project Explorer, select `Insert`, and then `Module`. This will create a new module.
Write the VBA Code: In the module, write a subroutine to refresh the PivotTable. Here’s a basic code example:
Customize the Code:

Change `”Sheet1″` to the actual name of the worksheet where your PivotTable is located.
If you have multiple PivotTables and you want to refresh a specific one, use `ws.PivotTables(“PivotTableName”)` instead of `ws.PivotTables(1)`.
You can also modify the message at the end or remove it if you don’t want a notification.

Run the Macro: You can now run the macro to refresh your PivotTable. Press `F5` within the VBA editor or go back to Excel, open the `Developer` tab, click `Macros`, select `RefreshPivotTable`, and click `Run`.

This VBA script automates the process of refreshing your PivotTable(s) whenever the underlying data changes, ensuring that your analyses and reports are always up to date with the latest information.

facebooklinkedin