Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Hiding a calculated PivotField

    I have a PivotTable where the Data area contains a (single) calculated field. I want to programmatically remove (i.e., hide) the calculated field and replace it with another field. When I record this, it reasonably enough creates a line like ----.PivotFields("Sum of amount2").Orientation = xlHidden.
    However, if I set it back to the calculated field then run the recorded macro, it errors on the line referenced above.
    It works as it should if you're hiding a "real" field, though.

    Has anybody seen this behavior and/or know of a work-around? I can't find anything in the Excel Knowledge Base.

    I'm working in Excel 2000, SR-1.
    The error message is: "Run-time error '1004': Unable to set the Orientation property of the PivotField class"

    An example is attached.

    TIA
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding a calculated PivotField

    I created a pivottable with the following macro: (I here assume the data is in the activeworksheet starting on cell A1)

    <pre>Sub CreateTable()
    Dim PVC As PivotCache
    Dim PT As PivotTable
    Set PVC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
    SourceData:=Range("A1").CurrentRegion.Address)
    Set PT = PVC.CreatePivotTable(tabledestination:="", tablename:="PivotTable1")
    With PT
    .PivotFields("Fruit").Orientation = xlRowField
    .PivotFields("Color").Orientation = xlColumnField
    .PivotFields("Amount").Orientation = xlDataField
    .CalculatedFields.Add "Amount2", "=2*Amount"
    .PivotFields("Amount2").Orientation = xlDataField
    .PivotFields("Sum of Amount").Orientation = xlHidden
    End With
    End Sub
    </pre>


    I cannot send the spreadsheet as I am working with a Dutch version and the line

    .PivotFields("Sum of Amount").Orientation = xlHidden

    should read

    .PivotFields("Som van Amount").Orientation = xlHidden

    in my version to let it work.
    I could not test it in an English version, but I think this will create a pivottable, create a calculatefield with 2 times the amount and then hides the original datafield. Give it a try an see how it works for you. Hope this helps.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hiding a calculated PivotField

    Hans - thanks for the reply. The code you wrote does work as you described, but it's not quite the problem I have. The starting point for what I need to do in VBA is an existing pivot table such as the one your code creates. The macro needs to replace the calculated field (amount2) with another field. I get an error on the line:
    .PivotFields("Sum of amount2").Orientation = xlHidden

    Incidentally I get the same error if I replace "Sum of amount2" with just "amount2". Ditto if I replace it with "Data". Finally, same error if I add the new datafield first, then try to delete the calc'd field.

    The only way I can figure to get rid of the calculated field is to clear the whole pivot table and recreate it (making sure I don't lose the cache in the process).

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding a calculated PivotField

    Sorry that I misunderstood. It has to do with the fact that you want to hide a calculated field. I don't have any experience with that, but maybe the slightly changed code below may do what you want:

    <pre>Sub CreateTable()
    Dim PVC As PivotCache
    Dim PT As PivotTable
    Dim fld As Object
    Set PVC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
    SourceData:=Range("A1").CurrentRegion.Address)
    Set PT = PVC.CreatePivotTable(tabledestination:="", tablename:="PivotTable1")
    With PT
    .PivotFields("Fruit").Orientation = xlRowField
    .PivotFields("Color").Orientation = xlColumnField
    .PivotFields("Amount").Orientation = xlDataField
    .CalculatedFields.Add "Amount2", "=2*Amount"
    .PivotFields("Amount2").Orientation = xlDataField
    .PivotFields("Som van Amount").Orientation = xlHidden
    For Each fld In _
    .CalculatedFields
    fld.Delete
    Next
    End With
    End Sub
    </pre>


  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hiding a calculated PivotField

    Thanks, Hans - that would be another work-around. Deleting the calculated PivotField does remove the field from the Data Area without causing an error. (I say work-around because I would still need to recreate the calculated field.)

Posting Permissions

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