How to build Pivot Tables with Excel VBA

Excel Pivot Tables have long been a key resource for data analysis

They have proven to be efficient, insightful and dynamic, allowing to slice and dice datasets and deliver comprehensive insights of datasets

Joining Pivot tables with VBA's efficiency brings to the next level data analysis capabilities.

VBA does not only allow to streamline analysis but offers also customization and automation tailored for specific needs 

This is where Excel VBA Pivot tables shine

This post will explain how to build Excel VBA Pivot tables by walking through the VBA code and providing insights in the key aspects. 

Once you have grasped a good understanding of the code in this post you might want to move to the MORE ADVANCED SOLUTIONS SHOWN HERE 

When Excel builds a PivotTable, there is a main process that occurs behind the scenes that needs to be replicated in the VBA code 

The data contained in the Excel range to be analysed, is moved to a cache memory. The Pivot table obtains the data from the Cache memory and organizes it according to the user's preferences

The steps that will be followed to build the code are the following

1. The object variables will be defined 

2. The code will check if a Pivot Table with the same name exists in the sheet in which the new Pivot Table will be placed. IN case it exists it will be deleted Excel does not allow to have 2 Pivot Tables with the same name in the same sheet.

3. The cache and the Pivot Table objects will be set

4. The data from the Excel table will be placed in the Pivot Table 

The code will run on a spreadsheet having a sheet called PTable in which the data used to build the PivotTable is reported

The final PivotTable will also be reported in the same sheet 

STEP 1

Define the variables. 

Verify if a PivotTable called "MyPivotTable" exists in the worksheet "Ptable". If it does it gets deleted

If the PivotTable exists VBA would return an error an block the execution of the code. To avoid the code from blocking the On Error Resume Next method is used, allowing to detect the error without interrupting the code

The if statement identifies if a PivotTable with the same name of the one to be created exists. If it does it gets deleted

A deeper explanation of Excel VBA Error Handling procedures can be found at the following article on Excel VBA Exception Handling  

Sub CreatePivotTable()

    Dim ws As Worksheet

    Dim pt As PivotTable

    Dim ptCache As PivotCache

    Dim ptRange As Range

    ' Check if PivotTable exists, delete it if it does

   On Error Resume Next

   Set pt = ThisWorkbook.Sheets("PTable").PivotTables("MyPivotTable")

   On Error GoTo 0

   If Not pt Is Nothing Then

        pt.TableRange2.Clear

   End If

STEP 2

The code identifies the range of data to be reported in the PivotTable 

The current region method is used because it allows to continue using the code in case new data is added to the range

Create the cache which is the object in which all the data for the pivot table is stored:

SourceTye = xlDatabase specidfies the source of the information

xlDataBase indicates that the source is an Excel table or range. Alternatively it can be an external source

(xlExternal) or multiple ranges (xlConsolidation)

SourceData indicates the range in which the data is containded

    ' Set the worksheet and range for PivotTable creation

    Set ws = ThisWorkbook.Sheets("PTable")

    Set ptRange = ws.Range("A1").CurrentRegion

    ' Create a new PivotCache

    Set ptCache = ThisWorkbook.PivotCaches.Create( _

        SourceType:=xlDatabase, _

        SourceData:=ptRange)

STEP 3

The pivot table is now created using the data stored in the cache (ptCache). It is placed in cell E1 of the sheet PTable and it given the name of MyPivotTable

Set pt = ptCache.CreatePivotTable: This part initializes a variable pt as a PivotTable. 

TableDestination:=ws.Cells(1, 5): This specifies where the pivot table will be placed in the worksheet (ws). In this case, it's being placed in the cell at the first row and fifth column (cell E1). 

TableName:="MyPivotTable": This sets the name of the pivot table to "MyPivotTable". 

     ' Create the PivotTable in the worksheet

    Set pt = ptCache.CreatePivotTable( _

        TableDestination:=ws.Cells(1, 5), _

        TableName:="MyPivotTable")

STEP 4

Creates the PivotTable and adds the fields from the Excel sheet 

It is worth noting that the reference to the column header is done through an index number. Number 1 indicates the first column in the 

data range and so on.

This allows to change the columns' name without having to amend the code

Alternatively we could have used the column name (.PivotFields("Name") although a change

in the column name would require to update the code reporting the new name given to the column

Setting the DisplayFieldCaption = False sets the PivotTable to not show the FieldHeaders

With pt

        .PivotFields(1).Orientation = xlRowField

        .PivotFields(2).Orientation = xlColumnField

        .AddDataField .PivotFields(3), "Sum of Quantity", xlSum 

        ' we do not want to dislay the field headers

        .DisplayFieldCaptions = False

End With

End Sub