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