Excel VBA: Updating Pivot Tables

This Excel VBA macro updates all the PivotTables on an active sheet.

Macro Example

Sub UpdatePivotTables_ActiveSheet_PivotCache_Refresh()

  Dim pvt As PivotTable

  For Each pvt In ActiveSheet.PivotTables
    pvt.PivotCache.Refresh
  Next pvt

End Sub

Description

Variable Declaration

Sub...

  Dim pvt As PivotTable

The 3rd line declares an object variable named pvt that will refer to a PivotTable object.

Starts Looping

Sub...

  Dim pvt As PivotTable

  For Each pvt In ActiveSheet.PivotTables

The 5th line starts to loop through all the elements (PivotTable) of a collection (Worksheet.PivotTables).

[Application.]ActiveSheet

The [Application.]ActiveSheet property returns an active Worksheet or Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

Because the ActiveSheet property is a member of the Excel.Global class, we can omit the Application property.

obj-Excel-Global-ActiveSheet

Worksheet.PivotTables

The Worksheet.PivotTables method returns a PivotTables collection object that represents all the pivot tables on a specified worksheet.

obj-Excel-Worksheet-PivotTables

Updating a PivotTable

Sub...

  Dim pvt As PivotTable

  For Each pvt In ActiveSheet.PivotTables
    pvt.PivotCache.Refresh

The 6th line refreshes the each pivot table.

PivotTable.PivotCache

pvt.PivotCache...

The PivotTable.PivotCache method returns a PivotCache object that represents the cache for the specified PivotTable.

PivotCache.Refresh

pvt.PivotCache.Refresh

The PivotCache.Refresh refreshes the PivotTable cache.

Loops Back

Sub...

  Dim pvt As PivotTable

  For Each pvt In ActiveSheet.PivotTables
    pvt.PivotCache.Refresh
  Next pvt

The 7th line loops back to get the next PivotTable object.

After every pivot table is updated, this macro ends.

Sub UpdatePivotTables_ActiveSheet_PivotCache_Refresh()

  Dim pvt As PivotTable

  For Each pvt In ActiveSheet.PivotTables
    pvt.PivotCache.Refresh
  Next pvt

End Sub

Properties

http://www.relief.jp/itnote/archives/018256.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007