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)