Adding calculated fields to a Pivot Table using VBA 

The CalculatedFields.Add method is used to create a new calculated field in a Pivot Table. Let's break down each element of this method.

Syntax of CalculatedFields.Add 

PivotTable.CalculatedFields.Add(Name, Formula, [UseStandardFormula])

1. The 'Name' parameter

The Name parameter specifies the name of the new calculated field. This is the label that will appear in the Pivot Table's field list. 

2. The 'Formula' parameter

The Formula parameter defines the calculation for the new field. This is the label that will appear in the Pivot Table's filed list

3. The 'UseStandardFormula' parameter

The 'UseStandardFormula' parameter is optional. It determines wether to use the standard formula (default is 'False'). When set to TRUE, it allows more complex formulas similar to those in Excel worksheet cells. 

EXAMPLE SYNTAX

PivotTable.CalculatedFields.Add "NewField", "=Field1 + Field2"

Let's walk through a practical example where we start with a sample Pivot Table and add a calculated field.

STEP-BY-STEP GUIDE

1. Set Up Your Data and Pivot Table

Assume you have  data in a worksheet named 'Data'

Create a Pivot Table and place it in a sheet called 'PivotTableSheet'. The pivot looks like this

2. Create the VBA code 

Open the VBA editor (ALT+F11) and insert a new module. Copy the following code in the module 

Sub AddCalculatedFieldToPivotTable()

    Dim ws As Worksheet

    Dim pt As PivotTable

    Dim ptCache As PivotCache

    ' Set the worksheet

    Set ws = ThisWorkbook.Sheets("PivotTableSheet")

    ' Assuming there's only one Pivot Table on the sheet

    Set pt = ws.PivotTables(1)

    ' Add a calculated field to the Pivot Table

    pt.CalculatedFields.Add "SalesPerUnit", "=Sales / Quantity"

    ' Refresh the Pivot Table to show the new calculated field

    pt.RefreshTable

End Sub

3. Run the Code

Run the 'AddCalculatedFieldToPivotTable'. This will add a new calculated field named 'SalesPerUnit' to the Pivot Table available fields

1. Define the WorkSheet and Pivot Table

Dim ws As Worksheet

Dim pt As PivotTable

Dim ptCache As PivotCache

Set ws = ThisWorkbook.Sheets("PivotTableSheet")

' Assuming there's only one Pivot Table on the sheet

Set pt = ws.PivotTables(1)

This part of the code sets the worksheet and identifies the Pivot Table you want to work with. 

2. Add the Calculated Field

pt.CalculatedFields.Add "SalesPerUnit", "=Sales / Quantity"

Here, we add a calculated field named "SalesPerUnit" with the formula =Sales / Quantity. This formula calculates the sales per unit for each product. 

3. Refresh the Pivot Table 

pt.RefreshTable

Refreshing the Pivot Table ensures the new calculated field is visible and the data is updated. 

Adding calculated fields to a Pivot Table using VBA can significantly enhance your data analysis capabilities in Excel. 

By understanding the CalculatedFields.Add method and following the practical example, you can automate the addition of calculated fields, saving time and reducing errors in your data analysis processes.

Feel free to experiment with different formulas and scenarios to see how calculated fields can best serve your needs