March 19, 2014

Create Pivot Tables Using VBA – Pivot Cache

Pivot Table
Pivot Table

Pivot Tables are MS Excel’s very powerful feature. Let’s learn how to create Pivot tables using VBA since we know how to create Pivot tables manually. The most attractive feature of a pivot table is that you can summarize thousands of rows of data in a manner you like and if you don’t like the summary, you can create another table in a few seconds.
Now Pivot tables do not summarize the data directly from the source but they use what is known as Pivot Cache. A pivot cache is an object that is invisible to the user. It is just a container that holds a copy of the source data in the computer’s memory. That is why you will notice that if you change your source nothing happens inside the pivot table. The pivot table is not automatically updated. If you, however, click inside the pivot table and then click the right mouse button and select ‘Refresh’ from the menu, your pivot table will also be updated. The updating of the pivot table happens via the pivot cache which is first ‘refreshed’.
Therefore we will first learn to create the pivot cache. We will next define the location of the source data. Then we’ll add the pivot table and finally using VBA we’ll define the location of the pivot table as we did when we created an embedded chart using VBA.

This is the VBA code we’ll use to perform the first steps:
Sub addCache()
ThisWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range(“A1″).CurrentRegion).CreatePivotTable _
TableDestination:=”R4C” & Range(“A1”).CurrentRegion.Columns.Count + 3
End Sub

The line of code
ā€œR4Cā€ & Range(ā€œA1ā€).CurrentRegion.Columns.Count + 3
is interpreted as the worksheet cell that is on row 4 of the column that is three columns to the right of the last column in the source range.

Once we click inside the outline of the created pivot table we can view the filelds from our source data. Also the areas where you can drag the fields become visible.
In the next video we’ll learn how to manipulate items in the pivot table.

Watch the Excel training video:

Further reading:
Creating PivotTable Reports and Charts with VBA in Excel 2010

Excel 2016 Pivot Table Data Crunching (includes Content Update Program) (MrExcel Library)

3 thoughts on “Create Pivot Tables Using VBA – Pivot Cache

  1. I have written this below code in VBA and getting Run time error…Please suggest

    Sub addCache()
    ThisWorkbook.PivotCaches.Add _
    (SourceType:=xlDatabase, _
    SourceData:=Range(“A1″).CurrentRegion).CreatePivotTable _
    TableDestination:=”R4C” & Range(“A1”).CurrentRegion.Columns.Count + 3
    End Sub

    1. Did you copy and paste the code from the website? Have a look at the ‘quotes’. Copy and paste causes such anomalies.

  2. Hello

    I’m facing an issue while creating a pivot cache in vba. I get a Type mismatch error.

    The code is:


    I have a code which is giving me an error, every time I try to create a pivot cache. The same code (with very slight changes) is working for another data sheet.

    I have not written the codes for inserting the pivot fields, since I am getting errors in this pivot cache for now. Please help.

    This is the code I have:

    Sub Comp_pvt()
    Dim PSheet As Worksheet ‘pivot sheet and data sheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range ‘ define the range of the pivot table
    Dim LastRow As Long, LastCol As Long ‘get the last row & column of our data range
    ‘Dim myfolder As String, myfile As String, filename As String

    Application.DisplayAlerts = False

    ‘insert a new sheet for the pivot table and the data sheet

    On Error Resume Next

    Sheets(“Compliance Pivots”).Delete

    On Error GoTo 0

    Sheets.Add.Name = “Compliance Pivots”

    Set PSheet = Sheets(“Compliance Pivots”) ‘destination sheet for the pivot
    Set DSheet = Sheets(“Comp_dedup”) ‘ source sheet for the pivot

    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

    ‘Error line
    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

    Set PTable = PCache.CreatePivotTable(tabledestination:=PSheet.Range(“a2″), tablename:=”Pivot”)

    End Sub

Comments are closed.