How To Refresh PivotTable using VBA

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to refresh PivotTable using VBA. Let’s get started

In the caption below, we have a sample data to the left and the supplementary data to the right. Note, the data is formatted in Excel Table. So, when we add the supplementary data to the main data, the table automatically grows

Create PivotTable

To create PivotTable:

  • In the Tables group of the Insert tab, click on PivotTable

  • In the Create PivotTable dialogue box, click on OK
  • Drag and drop Account Manager and Total into the Rows and Values areas respectively. In the caption below, we have our simple PivotTable report

VBA Editor Code

The next thing we need to do is to open the VBA editor and write to few lines of code to perform the operation. Therefore,

  • On the keyboard, deploy ALT + F11 to open the VBA Editor
  • In the Insert tab, click on Module
  • In the code window, execute the following lines of code as seen in the caption below

  • Close the VBA Editor
  • In the Insert tab, insert a desired shape and label
  • Next, right-click on the inserted shape and select Assign Macro
  • In the Assign Macro dialogue box, select PivotTable which is the sub routine name

  • Click OK
  • Next, copy all the supplementary values and add it to the main data
  • Click on the Shape Refresh Button

The data automatically updates. Cool


For the Youtube video, kindly click on the video below. Kindly do subscribe to my Youtube channel for more videos


Posted in Blog and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *