Excel VBA refresh all Pivot Table or Pivot Cache

This tutorial explains how to use Excel VBA to refresh all Pivot Table or Pivot Cache, and difference between PivotCache.Refresh and PivotTable.RefreshTable

You may also want to read:

Excel VBA refresh closed workbook

Excel automatically refresh pivot table

Excel VBA refresh all Pivot Table or Pivot Cache

Understanding difference between Pivot Table and Pivot Cache

To start with this topic, you should note the relationship and difference between refresh Pivot Table and Pivot Cache.

When you create a Pivot Table, Pivot Cache is created to store the source data in cache memory to speed up Pivot Table. Afterwards, Pivot Table will use the Pivot Cache as data source. If you are using multiple Pivot Tables using the same data source, all Pivot Tables use the same Pivot Cache in order to minimize your file size.

When you refresh a Pivot Table (right click on the Pivot Table > Refresh), you are refreshing Pivot Cache. Because Pivot Cache is refreshed, Pivot Table also gets refreshed. Therefore, you refresh all Pivot Tables that uses the same Pivot Cache by refreshing just one Pivot Table.

If you want Pivot Table to be independent, you can use Name Range to name the data source to other names (e.g Table1, Table2), and assign different name range to different Pivot Tables.

Read the below three VBA Methods regarding Refresh Pivot Table.

PivotTable.RefreshTable Method – Refreshes the PivotTable report from the source data. Returns True if it’s successful.

PivotTable.PivotCache().Refresh() Method – Updates the cache of the PivotTable object

Although VBA provides two different Methods, all of them will refresh Pivot Cache in the end, so it doesn’t matter which one to use. Therefore in the following sections, I will only show how to refresh Pivot Table.

Refresh all Pivot Table or Pivot Cache

To refresh all Pivot Table, there are two ways.

1) Using RefreshAll Method

The below action of VBA code is same as clicking Data > Refresh All

ActiveWorkbook.RefreshAll

This method is very simple and refreshes all Pivot Tables in the workbook, however, it also refreshes all other external connections.

2) Loop through individual Pivot Table and use RefreshTable Method

RefreshTable Method is same as right click on a Pivot Table and click “Refresh”. Although the below code looks like only refresh only one Pivot Table for each loop, but as I noted previously, refreshing one Pivot Table would refresh all other Pivot Tables using the same Pivot Cache.

Public Sub refreshPT()
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next
    Next
End Sub

Excel VBA refresh Pivot Table of specific name

The below code shows how to refresh Pivot Table of specific name (as noted above, this will also refresh Pivot Tables using the same Pivot Cache).

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

OR

ActiveSheet.PivotTables("PivotTable1").RefreshTable

You can also loop through each name  in the workbook.

Public Sub refreshPivot()
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            Select Case pt.Name
                Case "PivotTable1", "PivotTable2"
                    pt.RefreshTable
            End Select
        Next
    Next
End Sub

Change Pivot Table Layout using VBA

Change Pivot Table Layout, why?

To illustrate why we want to change Pivot Table Layout, lets see the below example.

Assume that we use the following data to create a Pivot Table. We want to show the total summary, group by Department, Empl ID.

pivot_table_layout_001

Excel 2003 – Tabular layout

Pivot Table layout is called “Tabular”, where each grouping is in one column, and the subtotal is at the bottom of each group.

pivot_table_layout_002

Excel 2007 and onwards – Compact layout

By default, Pivot Table layout is called “Compact”, where groupings are in one column, and subtotal is on top of each group.

Even worse, the column label “Department” and “Empl ID” are gone.

pivot_table_layout_003

I personally hate this layout because it does not use the actual column name, instead it uses “Row Labels”, “Column Labels”.

Since “Row Labels” refer to both Department and Empl ID as they display in one column, it uses a generic name “Row Labels”.

Excel 2007 and onwards – Outline layout

There is another new layout called “Outline”, where the layout format is between “Tabular” and “Compact”.

pivot_table_layout_004

Change Pivot Table Layout using VBA

Before continue reading, you should have knowledge in creating Pivot Table using VBA. If not, read the below article first

Create Pivot Table using Excel VBA

You can change the layout using VBA Code

ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlOutlineRow
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlCompactRow

Alternatively, you can define  Version and DefaultVersion when you create Pivot Cache and Pivot Table using VBA. Note that version for Pivot Cache and Pivot Table has to be the same, otherwise you will receive the following error.

Run-time error ‘5’: Invalid procedure call or argument

run_time_error5

Syntax of PivotCaches and CreatePivotTable

PivotCaches.Create(SourceType, SourceData, Version)
CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

The DefaultVersion of Excel 2013 is xlPivotTableVersion15 while Excel 2003 is xlPivotTableVersion10. (Version10 is what I tested in Excel 2003 although the below table from MSDN shows Version11, but they should have no difference)

NameValueDescription
xlPivotTableVersion20000Excel 2000
xlPivotTableVersion101Excel 2002
xlPivotTableVersion112Excel 2003
xlPivotTableVersion123Excel 2007
xlPivotTableVersion144Excel 2010
xlPivotTableVersion155Excel 2013
xlPivotTableVersionCurrent-1Provided only for backward compatibility

Below is an example to create  a 2003 version Pivot Table in Excel 2013

    ActiveWorkbook.PivotCaches. _
        Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("Sheet1").Range("A1:B7"), Version:=xlPivotTableVersion10). _
            CreatePivotTable _
            TableDestination:=Worksheets("Sheet5").Range("A1"), _
            TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Rename RowLabels and ColumnLabels of Compact Layout

You can change any label in the Pivot Table.

To change RowLabels and Column Labels

ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "NewRowName"
ActiveSheet.PivotTables("PivotTable1").CompactLayoutColumnHeader = "NewColumnName"

pivot_table_layout_005

To change “Grand Total”

ActiveSheet.PivotTables("PivotTable1").GrandTotalName = "NewGrandTotal"

VBA_pivottable_006

To change name of Pivot Items

Any other names you see in the Pivot Table are called “Pivot Items” (such as Department name, Empl ID), the name can be changed through Caption Property.

Below example shows how to change the Pivot Items “Sum of Salary” to “NewSumOfSalary”.

ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of Salary").Caption = "NewSumOfSalary"

pivot_table_layout_007

What’s next?

Now you have created a Pivot Table, and changed the layout you want. The next step is to play around with Filters in VBA. You are recommended to read the article.

Excel VBA multiple criteria for Label Filter of Pivot Table

Outbound References

http://msdn.microsoft.com/en-us/library/office/ff837609%28v=office.15%29.aspx

https://www.youtube.com/watch?v=M04rnVoD5mc

Create Pivot Table using Excel VBA

This Excel tutorial explains how to create Pivot Table using Excel VBA, set Print Area for Pivot Table

Create Pivot Table using Excel VBA

Create Pivot Table using Excel VBA is very straight forward and easy to use. If you don’t understand any Object or Property that VBA Pivot Table uses, simply use “Record Macro” function to do recording to see the underlying VBA code behind each action, such as create Pivot Table, change Pivot Table Fields, select Critera, delete Pivot Table, etc.

In this post, I will summarize the code for the basic actions to save your time.

If you don’t know how to use “Record Macro” function, click to read the below article.

Excel Record Macro

Create Empty Pivot Table

The follow example shows how to create a Pivot Table in Sheet5 A1, from data source in Sheet1 A1:B7

Source table:

VBA_pivottable_000

Public Sub createPivot()
    ActiveWorkbook.PivotCaches. _
        Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("Sheet1").Range("A1:B7")). _
            CreatePivotTable _
            TableDestination:=Worksheets("Sheet5").Range("A1"), _
            TableName:="PivotTable1"
End Sub
VBA CodeTypeMeaningExample
PivotCachesObjectPivot Table stores its source data and workings in cache memory called PivotCaches, in order that Pivot Table can run faster
Source TypePropertyType of data source, parameters include:
xlConslidation
xlDatabase
xlExternal
xlPivotTable
xlScenario
xlDatabase
Source DataPropertyWhere the source data locateWorksheets(“Sheet1”).Range(“A1:B7”)
TableDestinationPropertyWhere the Pivot Table is to be placedWorksheets(“Sheet5”).Range(“A1”)
TableNamePropertyThe name for Pivot TablePivotTable1

Below is the result you will get from the code, just an empty Pivot Table with no fields.

VBA_pivottable_001

Add PivotFields to Pivot Table

Now try to add Employee ID to Row, Audit to Column, and then Count Employee ID.

VBA_pivottable_002

Public Sub createPivot()
    ActiveWorkbook.PivotCaches. _
        Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("Sheet1").Range("A1:B7")). _
            CreatePivotTable _
            TableDestination:=Worksheets("Sheet5").Range("A1"), _
            TableName:="PivotTable1"
        Set PT = Worksheets("Sheet5").PivotTables("PivotTable1")
        With PT.PivotFields("Empl ID")
            .Orientation = xlRowField
            .Position = 1
        End With
        With PT.PivotFields("Department")
            .Orientation = xlColumnField
            .Position = 1
        End With
         With PT.PivotFields("Empl ID")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlCount
        End With  
End Sub   

The above code is very straight forward and easy to understand, each color represents each set of actions.

The first action is to add Row, then add Column, finally add Data and apply aggregate function “count”. (default is “sum” if Function Property is omitted for DataField)

If you want to add multiple column / row, just copy the code from “With” to “End With”, then change the PivotFields name and then change Position number to increase by 1.

VBA CodeTypeMeaningExample
PivotFieldsObjectColumn data in your data sourceEmpl ID, Department
OrientationPropertyWhere you want to place your column data, parameters include:
xlRowField       (Rows)
xlColumnField   (Columns)
xlPageField       (Filter)
xlDataField       (Values)
xlHiddenFields
PositionPropertyThe order of OrientationIf both Empl ID and Department are added as xlColumnField, data will group by position 1 and then by position 2
CaptionPropertyRename PivotField. You can see the new name in the Pivot Table if using 2003 layout
FunctionPropertyAggregate function or statistics function of the data field, parameter include
xlAverage
xlCount
xlCountNums (count numeric values)
xlMax
xlMin
xlProduct (multiply all data)
xlStDev (standard deviation of sample)
xlStDevP (standard deviation of population)
xlSum
xlVar (variance of sample)
xlVarP (variance of population)

Set Print Area for Pivot Table using VBA

Sub printPivot()
 ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
 ActiveSheet.PageSetup.PrintArea = Selection.Address
End Sub

What’s Next?

Now you have created a Pivot Table below, which is known as a “Compact Layout”. If you remember in Excel 2003, the layout is quite different (at least 2003 does not have “Row Labels” and “Column Labels”).

VBA_pivottable_002

Read the below article to change a different layout.

Change Pivot Table Layout using VBA

 

Outbound References

http://msdn.microsoft.com/en-us/library/office/aa215695%28v=office.11%29.aspx

https://www.youtube.com/watch?v=M04rnVoD5mc

Excel VBA multiple criteria in Label Filter of Pivot Table

This Excel VBA tutorial explains how to apply multiple criteria in Label Filter of Pivot Table.

Problem with multiple criteria in Label Filter of Pivot Table

There are three kinds of Filters in Pivot Table for each Pivot Filters – Label Filter, Value Filter, Manual Filter. In this tutorial, I will focus on Label Filter, because I use “Department” as an example, which is non-numerical.

Suppose you have the below Pivot Table and you want to show Department Name contains “Fin” and “Aud”.

pivotFilters_01

Click on Department arrow and you can select criteria using Filters. You can see a list of options here for you to make criteria on Department. Now click “Contains…”

pivotFilters_02

You will immediately notice that you can only add one condition (so do other condition options), but we really need to use multiple conditions such as AND, OR

pivotFilters_03

Now go back to the previous option page, you can simply uncheck the HR and IT box. Although you can do the job reluctantly, the action is manual.

pivotFilters_04

Syntax of PivotFilters

Before continue reading, you should have knowledge in creating Pivot Table and change Pivot Table layout. If not, please read the below articles.

Create Pivot Table using Excel VBA

Change Pivot Table Layout using VBA

To add criteria in Pivot Table, we have to use Add Property of PivotFilters.

Below is the syntax of PivotFilters.Add Property

PivotFilters.Add(Type, DataField, value1, Value2, Order, Name, Description, IsMemberPropertyFilter, MemberPropertyField)
NameRequired/OptionalData TypeDescription
TypeRequiredXlPivotFilterTypeRequires an XlPivotFilterType type of filter.
DataFieldOptionalVariantThe field to which the filter is attached.
Value1OptionalVariantFilter value 1.
Value2OptionalVariantFilter value 2.
OrderOptionalVariantOrder in which the data should be filtered.
NameOptionalVariantName of the filter.
DescriptionOptionalVariantA brief description of the filter.
MemberPropertyFieldOptionalVariantSpecifies the member property field on which the label filter is based.

 

The first argument Type determines how you want to compare the data in the filter. Some “Type” such as xlValueIsBetween requires two value (Value1, Valu2), but “Value2” does not mean you can add two criteria using one Filter.

Below are the values of Type

NameValueDescription
xlBefore31Filters for all dates before a specified date
xlBeforeOrEqualTo32Filters for all dates on or before a specified date
xlAfter33Filters for all dates after a specified date
xlAfterOrEqualTo34Filters for all dates on or after a specified date
xlAllDatesInPeriodJanuary53Filters for all dates in January
xlAllDatesInPeriodFebruary54Filters for all dates in February
xlAllDatesInPeriodMarch55Filters for all dates in March
xlAllDatesInPeriodApril56Filters for all dates in April
xlAllDatesInPeriodMay57Filters for all dates in May
xlAllDatesInPeriodJune58Filters for all dates in June
xlAllDatesInPeriodJuly59Filters for all dates in July
xlAllDatesInPeriodAugust60Filters for all dates in August
xlAllDatesInPeriodSeptember61Filters for all dates in September
xlAllDatesInPeriodOctober62Filters for all dates in October
xlAllDatesInPeriodNovember63Filters for all dates in November
xlAllDatesInPeriodDecember64Filters for all dates in December
xlAllDatesInPeriodQuarter149Filters for all dates in Quarter1
xlAllDatesInPeriodQuarter250Filters for all dates in Quarter2
xlAllDatesInPeriodQuarter351Filters for all dates in Quarter3
xlAllDatesInPeriodQuarter452Filters for all dates in Quarter 4
xlBottomCount2Filters for the specified number of values from the bottom of a list
xlBottomPercent4Filters for the specified percentage of values from the bottom of a list
xlBottomSum6Sum of the values from the bottom of the list
xlCaptionBeginsWith17Filters for all captions beginning with the specified string
xlCaptionContains21Filters for all captions that contain the specified string
xlCaptionDoesNotBeginWith18Filters for all captions that do not begin with the specified string
xlCaptionDoesNotContain22Filters for all captions that do not contain the specified string
xlCaptionDoesNotEndWith20Filters for all captions that do not end with the specified string
xlCaptionDoesNotEqual16Filters for all captions that do not match the specified string
xlCaptionEndsWith19Filters for all captions that end with the specified string
xlCaptionEquals15Filters for all captions that match the specified string
xlCaptionIsBetween27Filters for all captions that are between a specified range of values
xlCaptionIsGreaterThan23Filters for all captions that are greater than the specified value
xlCaptionIsGreaterThanOrEqualTo24Filters for all captions that are greater than or match the specified value
xlCaptionIsLessThan25Filters for all captions that are less than the specified value
xlCaptionIsLessThanOrEqualTo26Filters for all captions that are less than or match the specified value
xlCaptionIsNotBetween28Filters for all captions that are not between a specified range of values
xlDateBetween32Filters for all dates that are between a specified range of dates
xlDateLastMonth41Filters for all dates that apply to the previous month
xlDateLastQuarter44Filters for all dates that apply to the previous quarter
xlDateLastWeek38Filters for all dates that apply to the previous week
xlDateLastYear47Filters for all dates that apply to the previous year
xlDateNextMonth39Filters for all dates that apply to the next month
xlDateNextQuarter42Filters for all dates that apply to the next quarter
xlDateNextWeek36Filters for all dates that apply to the next week
xlDateNextYear45Filters for all dates that apply to the next year
xlDateThisMonth40Filters for all dates that apply to the current month
xlDateThisQuarter43Filters for all dates that apply to the current quarter
xlDateThisWeek37Filters for all dates that apply to the current week
xlDateThisYear46Filters for all dates that apply to the current year
xlDateToday34Filters for all dates that apply to the current date
xlDateTomorrow33Filters for all dates that apply to the next day
xlDateYesterday35Filters for all dates that apply to the previous day
xlNotSpecificDate30Filters for all dates that do not match a specified date
xlSpecificDate29Filters for all dates that match a specified date
xlTopCount1Filters for the specified number of values from the top of a list
xlTopPercent3Filters for the specified percentage of values from a list
xlTopSum5Sum of the values from the top of the list
xlValueDoesNotEqual8Filters for all values that do not match the specified value
xlValueEquals7Filters for all values that match the specified value
xlValueIsBetween13Filters for all values that are between a specified range of values
xlValueIsGreaterThan9Filters for all values that are greater than the specified value
xlValueIsGreaterThanOrEqualTo10Filters for all values that are greater than or match the specified value
xlValueIsLessThan11Filters for all values that are less than the specified value
xlValueIsLessThanOrEqualTo12Filters for all values that are less than or match the specified value
xlValueIsNotBetween14Filters for all values that are not between a specified range of values
xlYearToDate48Filters for all values that are within one year of a specified date

 

Apply single criteria in PivotFilters

To filter department that contains string “Fin”

ActiveSheet.PivotTables("PivotTable3").PivotFields("Department").PivotFilters.Add Type:=xlCaptionContains, Value1:="Fin"

The above example has the same effect as typing “Fin” in Label Filter

pivotFilters_03

Cancel criteria in Label Filter

Method for PivotFieldsExplanation
ClearValueFiltersClear only Value Filter
ClearLabelFiltersClear only Label Filter
ClearManualFiltersClear only Manual Filter (items your manually select or uncheck)
ClearAllFiltersClear all Value, Label and Manual Filters

The below example cancels all Filters for pivot field “Department”.

ActiveSheet.PivotTables("PivotTable3").PivotFields("Department").ClearAllFilters

In order to cancel all Filters of all PivotFields, loop through all PivotFields in PivotTable

Public Sub cancelAll()
    For Each ptf In ActiveSheet.PivotTables("PivotTable1").PivotFields
        ptf.ClearAllFilters
    Next
End Sub

Apply multiple criteria in PivotFilters

Same old problem as in user interface of Label Filter, we cannot add multiple criteria under one Filter even in VBA.

To work around, we need to simulate the effect of “checking” and “unchecking” the box but also adding the function to do the “Contain” filter.

pivotFilters_04

The department name (Audit, Finance…) under a “Department” (a PivotFields) is referred to as “PivotItems”. Each PivotFields has its own array of PivotItems.

We can loop through each PivotItems (Audit, Finance…) in PivotFields “Department” to check the name of each department.

Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Department")
        For i = 1 To .PivotItems.Count
            If InStr(1, .PivotItems(i), "Fin")<>0 Or InStr(1, .PivotItems(i), "Aud")<>0 Then
                .PivotItems(i).Visible = True
            Else
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
End Sub

What I did in the above code is to loop through each PivotItems (Audit, Finance…) under “Department”, and check the name to see if it contains “Fin” or “Aud” using INSTR function (return 0 if substring not found, return the position of first occurrence if found) . If found, show them, otherwise hide them.

Here is how you can simulate the rest of the criteria in Label Filter.

EqualPivotItems(i) = “xxx”
Does Not EqualPivotItems(i) <> “xxx”
Begins Withleft(PivotItems(i),len(“xxx”)) = “xxx”
Does Not Begin Withleft(PivotItems(i),len(“xxx”)) <> “xxx”
Ends Withright(PivotItems(i),len(“xxx”)) = “xxx”
Does Not End Withright(PivotItems(i),len(“xxx”)) <> “xxx”
ContainsInstr(1,PivotItems(i),”xxx”) <> 0
Does Not ContainInstr(1,PivotItems(i),”xxx”) = 0

Select specific number of PivotItems

Instead of using text to filter, we can specify the how many PivotItems we want to return. In the below example, I want to select only the 2nd to 5th PivotItems in alphabetic order.

Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
        For i = 1 To .PivotItems.Count
            If i >= 2 And i <= 5 Then
                .PivotItems(i).Visible = True
            Else
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
End Sub

In the below example, I want to exclude the 1st to 5th department.

Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
        For i = 1 To .PivotItems.Count
            If i >5 Then
                .PivotItems(i).Visible = True
            Else
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
End Sub

In the below example, I want to exclude the 1st to 5th department.

Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
        For i = 1 To .PivotItems.Count
            If i >5 Then
                .PivotItems(i).Visible = True
            Else
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
End Sub

In the below example, I want to include the 1st to 5th department plus Finance department

Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
        For i = 1 To .PivotItems.Count
            If i <=5 OR InStr(1, .PivotItems(i), "Fin")>0 Then
                .PivotItems(i).Visible = True
            Else
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
End Sub

Outbound References

http://msdn.microsoft.com/zh-tw/library/office/ff193582%28v=office.14%29.aspx