Writing VBA Scripts to Add Calculated Fields in Pivot Tables 

To start let's set up a basic Excel sheet with sample data 

The first step is to access the VBA editor

1. Press ALT + F11 to open the VBA editor

2. In the VBA editor go to 'Insert -> Module' to create a new module 

3. Copy and pate the following VBA code into the module:

Sub CreatePivotTable()

 Sub CreatePivotTable()

    Dim ws As Worksheet

    Dim dataRange As Range

    Dim pivotTableRange As Range

    Dim pivotTableSheet As Worksheet

    Dim pivotTable As PivotTable

    Dim pivotTableCache As PivotCache

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set dataRange = ws.Range("A1:E6") ' Adjust the range as necessary

    ' Add a new worksheet for the Pivot Table

    Set pivotTableSheet = ThisWorkbook.Sheets.Add

    pivotTableSheet.Name = "PivotTableSheet"

    ' Define the range for the Pivot Table

    Set pivotTableRange = pivotTableSheet.Range("A1")

    ' Create the Pivot Table cache

    Set pivotTableCache = ThisWorkbook.PivotCaches.Create( _

        SourceType:=xlDatabase, _

        SourceData:=dataRange)

    ' Create the Pivot Table

    Set pivotTable = pivotTableCache.CreatePivotTable( _

        TableDestination:=pivotTableRange, _

        TableName:="SalesPivotTable")

    ' Set up the Pivot Table fields

    With pivotTable

        .PivotFields("Product").Orientation = xlRowField

        .PivotFields("Date").Orientation = xlColumnField

        .PivotFields("Sales").Orientation = xlDataField

        .PivotFields("Quantity").Orientation = xlDataField

    End With

End Sub

Below is the VBA code to add a calculated filed to our Pivot Table.

We will add a calculated field that calculates the 'Average Price per Unit'

Sub AddCalculatedFields()

    Dim pivotTable As PivotTable

    Dim pivotTableSheet As Worksheet

    ' Set the worksheet and Pivot Table

    Set pivotTableSheet = ThisWorkbook.Sheets("PivotTableSheet")

    Set pivotTable = pivotTableSheet.PivotTables("SalesPivotTable")

    ' Add a calculated field for Average Price per Unit

    pivotTable.CalculatedFields.Add "Average Price per Unit", "=Sales/Quantity"

    pivotTable.PivotFields("Average Price per Unit").Orientation = xlDataField

End Sub

EXPLANATION

pivotTable: refers to the Pivot Table we created earlier 

Calculated Field 'Average Price per Unit' is calculated by dividing 'Sales' by 'Quantity'

To run the scripts:

1. Close the VBA Editor.

2. Press Alt + F8 to open the Macro dialog box.

3. Select CreatePivotTable and click Run.

4. After the Pivot Table is created, open the Macro dialog box again.

5. Select AddCalculatedFields and click Run.

After running both scripts  run the scripts, your Pivot Table includes the calculated field 'Average Price per Unit'

Let's look at how our pivot table should look like after running the code. Below is a snapshot only for the 1st of January

If you want to run the code more than once, before launching the macro remember to delete the sheet 'PivotTableSheet'

Using VBA to add calculated fields in Pivot Tables can greatly enhance your data analysis capabilities in Excel. By following this guide, you have learned how to create a Pivot Table and add calculated fields using VBA, which can save you time and effort in managing your data.

Key Takeaways

• VBA Automation: Automating Pivot Tables with VBA increases efficiency and accuracy.

• Calculated Fields: Adding calculated fields allows for more complex data analysis directly within your Pivot Table.

• Practical Application: Practice with your own datasets to better understand and implement these techniques.