How to add calculated fields to Pivot Tables with VBA

In this post we have described how to use VBA to create Pivot Tables

We will now bring the exercise one step further by adding some calculated fields allowing to bring data analysis capabilities to the next level 

The key steps to create a Pivot Table with VBA are:

1. Set the variables for the cache and the pivot table

2. Create sheet to host the pivot table and manage errors

3. Create the pivot table 

4. Add the fields to the pivot table including calculated fields 

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 code will run on a spreadsheet having a sheet called Data in which the data used to build the PivotTable is reported

The final PivotTable will be reported in a separate sheet called "PivotSheet". 

The starting data is presented as shown in the below picture

Define the variables for the Cache, the Pivot table and the range from which the data  will be taken to create the PivotTable

Dim PTCache As PivotCache

Dim PT As PivotTable

Dim rng As Range

Alerts are switched off in order to avoid being displayed when the code runs  

Application.ScreenUpdating = False

Application.DisplayAlerts = False

The sheet Data is selected and the range containing the data in the sheet is identified 

The range is then allocated to the variable rng 

Sheets("Data").select

lastRow = Sheets("Data").Cells(Sheets("Data").Rows.Count, 1).End(xlUp).Row

Set rng = Sheets("Data").range(cells(1,1),cells(lastRow,6))

The sheet "Pivotsheet" is deleted. The deletion occurs in case the code was previously run and a sheet with this name exists

In case the sheet does not exists the code would normally stop as it is asked to delete a sheet that does not exist

In order to avoid from stoping the error handling procedure "On Error Resume Next" has been foresee in order to allow the code execution to continue in case the sheet does not exist

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

On Error Resume Next

Sheets("Pivotsheet").Delete

On Error GoTo 0

The range containing the data is pplaced in the variable rng 

ActiveSheet.Name = "Pivotsheet"

Set rng = Sheets("Data").Range(Cells(1, 1), Cells(lastRow, 6))

The sheet is created and named "Pivotsheet" 

Worksheets.Add

ActiveSheet.Name = "Pivotsheet"

The cache is created and the range containing the data is provided as the source

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 contained. The variable rng is used for this purpose

Set PTCache = ActiveWorkbook.PivotCaches.Create( _

    SourceType:=xlDatabase, _

    SourceData:= rng)

The pivot table is created  and instruction is given to be placed starting from cell A1 f the "PivotSheet" and it is given the name "Food"

Set PT = PTCache.createpivottable( _

        TableDestination:=Range("a1"), _

        TableName:="Food")

The data from the excel range is inserted in the pivot table 

The columns of the excel range are referred to by their number

Column 1 (Name) is inserted in the filter section of the pivot table 

Column 2 (Food) will represent the rows and column 4 (origin) the columns

Column 3 (Quantity) and column 5 ( Season) will be reported in the data field

With PT

    .PivotFields(1).Orientation = xlPageField

    .PivotFields(2).Orientation = xlRowField

    .PivotFields(4).Orientation = xlColumnField

    .PivotFields(3).Orientation = xlDataField

    .PivotFields(5).Orientation = xlDataField

A new calculated field is created in which the Average of the division of Quantity column by the Orders column .

The calculated field is obtained by taking the values already present in the pivot table and  performing the desired calculation. 

 The new measure is given the name "Average Order" and it is then reported in the data field

 .CalculatedFields.Add "Average Order", "=Quantity / Orders", xlAverage

    .PivotFields("Average Order").Orientation = xlDataField

  .DisplayFieldCaptions = False

End With

The variables are set to Nothing and the settings are set back to previous

Set PTCache = Nothing

Set PT = Nothing

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub