Adding calculated field using VBA

Question

am trying to add a calculated field to a pivot table created in VBA (Excel 2010). The pivot table is working and everything is appearing- except for the calculated field which is completely absent.

The code I am using is as follows:

    Sub Create_Pivot_Table_for_chart2()
 Dim wsnew As Worksheet
 Dim objPivotcache As PivotCache
 Dim objPivotTable As PivotTable

'Adding new worksheet
 Set wsnew = Worksheets.Add
 wsnew.Name = "Test5"

'Creating Pivot cache
 Set objPivotcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, "'datasheet'!B1:BX1000")

'Creating Pivot table
 Set objPivotTable = objPivotcache.CreatePivotTable(wsnew.Range("A1"))

'Setting Fields
 With objPivotTable
 'set row field
 With .PivotFields("Prosperator")
 .Orientation = xlRowField
 .Position = 1
 End With

 'set column field
 With .PivotFields("Business Name")
 .Orientation = xlRowField
 .Position = 2
 End With

  'set calculated field
 .CalculatedFields.Add "TOGrowth%", "= ('ITD Average'- 'Pre-ignition T/O')/'Pre-ignition T/O'"

 'set data field
 .AddDataField .PivotFields("Pre-ignition T/O"), "PI T/O", xlSum
 .AddDataField .PivotFields("ITD Average"), "ITD", xlSum


 End With
END SUB

Thanks

Answer 1

Looking at your code, you haven't added the calculated field to the pivot table as a data field.

You need to add the following line of code after you've created the field:

.PivotFields("TOGrowth%").Orientation = xlRowField