Use VBA To Refresh Pivot Table

Pivot Table is a useful tool in MS Excel. It summaries the data from Excel or other external databases such as MS Access, MS SQL, MySQL, AS400 etc.

Normally, right click at the pivot table, then choose 'Refresh' from the shortcut menu to update the data. You need to click twice to finish this process. If you prefer to simplify this step, create a button and have a simple VBA code on it to update the pivot table.

1. Know the pivot table name. Right click at the pivot table, click at 'Table Options'.

This pivot table name is PivotTable1. This name is important during the VBA code creation.



2. Create a Command Button. We will put VBA code inside this button. Future data update will become simple and fast with just a click at the button.

Find Control Toolbox. Click at View> Toolbars > Control Toolbox

Select the Command Button.


Locate a place to draw the button.



3. Change the button display name. Right click the button to find 'Properties'. Under 'Caption' change the name to 'Refresh'.



4. Create VBA Code. Close the Properties window, double click the button. A Visual Basic windows will pop up with some coding appear like this:

Private Sub CommandButton1_Click()

End Sub


Add ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh between these two lines. This command will refresh 'PivotTable1' in the sheet where the button is pressed.

The complete code should look as follow:-

Close Visual Basic windows after this.


5. To complete the process. Return to the Control Toolbox and click at the first button to exit the design mode. Close the Control Toolbox after this.


Now, when you click the button, the pivot table will be updated.