VBA- Updating Pivot Table

The pivot table is an important feature to explore, summarize, and interpret the bulk amount of data. It helps data evaluating, reviewing, as well as making useful interpretations of it. But the problem with the Pivot table is that it needs to be updated by the user every time (manually) whenever there is any change in the data. Unlike in the below, if you change a value in B4 cell i.e., 400 to 500, but you will notice the Pivot table still shows the previous data without undergoing any change. The sheet has to be refreshed for any changes to reflect in the Pivot Table. If there are multiple Pivot tables, it becomes very tedious and time-consuming. But with the invent of VBA, this process has become easy, and the user can skip the manual refreshing with just a few lines of code.

The steps for refreshing or updating a Pivot Table through VBA are as follows:

  1. Defining Worksheet Event

Double click on the sheet. It will direct you to a blank sheet. Then, from the top select the worksheet and select the event to ‘Change’.

VBA Updating Pivot Table
VBA Updating Pivot Table
VBA Updating Pivot Table
  • Defining Worksheet Object

We will be using the worksheet object in between the sub procedures.

Program:

VBA Updating Pivot Table
  • Referring Pivot Table

Here we will be referring to the Pivot table by its name (in the below example, we have used the same name for the sheet as well as for Pivot Table).

Program:

VBA Updating Pivot Table
  • Refresh Table Method

Program:

VBA Updating Pivot Table

Refreshing multiple Pivot Tables

In big projects, usually, the VBA developer adds separate Pivot Tables for each report, in separate work sheets within the same workbook. In these cases, it will become tedious if we keep on writing the VBA refreshing code for each pivot table for separate sheets.

The only solution for the above problem is to write a single code wherein we can loop through the various sheets and refresh all the Pivot tables once with a single click.

Program: With Pivot Table object

Example 2: With Pivot Cache

Worksheet Deactivate Event

Whenever we refresh any worksheet by using the “Worksheet Change” event, it keeps on refreshing everything even if there is no alters in the data and a change occurs in the worksheet and there is no change in the data source

Even if the user enters a comma or dot in the worksheet the VBA code refreshes the complete pivot table. Thus, increasing the loading time for the program unnecessarily. But this could be avoided by using the “Worksheet Deactivate” method instead of the “Worksheet Change” method.

Program:

VBA Updating Pivot Table

Pin It on Pinterest

Share This