Unable to get the PivotFields property of the PivotTable Class

[DaveH1]

Hi
I'm trying to create a pivot table with a calculated field using the following code. When I get to the last line, I get the mesage "Unable to get the PivotFields property of the PivotTable Class." I'd really appreciate any help in figuring out why. Thank you very much.

DaveH1

Sub Main()
Dim PTcache As PivotCache
Dim pt As PivotTable

Application.ScreenUpdating = False
' Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0

' Create a Pivot Cache
Set PTcache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
' Add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
ActiveWindow.DisplayGridlines = False
' Create the Pivot Table from the Cache
Set pt = ActiveSheet.PivotTables.Add( _
PivotCache:=PTcache, _
TableDestination:=Range("A1"), _
TableName:="EngagementPivot")

With pt
' Add fields for filtering
.PivotFields("PhoneAvailable").Orientation = xlPageField
.PivotFields("CanCall?").Orientation = xlPageField
.PivotFields("Referred?").Orientation = xlPageField
.PivotFields("Month&Year_Expected_Start").Orientation = xlPageField
.PivotFields("Month&Year_Actual_Start").Orientation = xlPageField
.PivotFields("DeliveryConsultant").Orientation = xlPageField
.PivotFields("SalesConsultant").Orientation = xlPageField
.PivotFields("Scheduler").Orientation = xlPageField
.PivotFields("ProgramFeeRange").Orientation = xlPageField

' Add fields for rows
.PivotFields("OriginatingFirm").Orientation = xlRowField
.PivotFields("DeliveringFirm").Orientation = xlRowField

' Add fields for non-calculated values
.PivotFields("ProgramFee").Orientation = xlDataField
.PivotFields("#Engaged").Orientation = xlDataField
.PivotFields("$Engaged").Orientation = xlDataField
.PivotFields("#atRisk").Orientation = xlDataField
.PivotFields("$atRisk").Orientation = xlDataField

' Add a calculated field to compute percent engagement
.CalculatedFields.Add "Engaged_%", "=$Engaged/ProgramFee"
.PivotFields("Engaged_%").Orientation = xlDataField

 

[Marcelo Branco]

Hi Dave,

For testing-purposes:

Comment the last 2 code-lines and try to create the PVT

If it works, turn-on the macro-recorder and add the calculated field.

Take a look at the recorded macro

M.

 

[DaveH1]

Thank you,
When I commented out the code, it worked. Then I added the calculated field with a macro and here is the code it produced.

Sub AddCalculatedField()
'
' AddCalculatedField Macro
'
'
ActiveSheet.PivotTables("EngagementPivot").CalculatedFields.Add _
"EngagementPercent", "='$Engaged' /ProgramFee", True
ActiveSheet.PivotTables("EngagementPivot").PivotFields("EngagementPercent"). _
Orientation = xlDataField
End Sub

That worked so I guess I'm ok.

Thank you very much
Dave

 

[Marcelo Branco]

Dave,

Good job!

You are welcome and tks for the feedback

M.