VBA Updating Pivot Table

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’.

• Defining Worksheet Object

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

Program:

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheet ("Pivot Table")
End Sub

• 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:

Private Sub Worksheet_Change (ByVal Target As Range)
    Worksheet ("Pivot Table").PivotTables ("Pivot Table")
End Sub

• Refresh Table Method

Program:

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheet("Pivot Table").PivotTables("PivotTable1").RefreshTable
End Sub

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

Sub Refreshing_Multiple_Pivot_Tables_Example1()
  Dim PT As PivotTable
  For Each PT In ActiveWorkbook.PivotTables
    PT.RefreshTable
  Next PT
End Sub

Example 2: With Pivot Cache

Sub Refreshing_Mutiple_Pivot_Tables_Example2()
  Dim PT As PivotTable
  Dim PC As PivotCache
  For Each PC In ActiveWorkbook.PivotCaches
    PC.Refresh
  Next PT
End Sub

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:

Private Sub Worksheet_Deactivate()
    Worksheet("Pivot Table").PivotTables("PivotTable1").RefreshTable
End Sub