Results 1 to 4 of 4

Unable to get the PivotFields property of the PivotTable Class

This is a discussion on Unable to get the PivotFields property of the PivotTable Class within the Excel Questions forums, part of the Question Forums category; Hi I'm trying to create a pivot table with a calculated field using the following code. When I get to ...

  1. #1
    New Member
    Join Date
    Aug 2011
    Posts
    2

    Default Unable to get the PivotFields property of the PivotTable Class

    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

  2. #2
    MrExcel MVP Marcelo Branco's Avatar
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,515

    Default Re: Unable to get the PivotFields property of the PivotTable Class

    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.

  3. #3
    New Member
    Join Date
    Aug 2011
    Posts
    2

    Default Re: Unable to get the PivotFields property of the PivotTable Class

    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

  4. #4
    MrExcel MVP Marcelo Branco's Avatar
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,515

    Default Re: Unable to get the PivotFields property of the PivotTable Class

    Dave,

    Good job!

    You are welcome and tks for the feedback

    M.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com