Remove DataField in Pivot table using VBA

[AnilPullagura]

Pros,

Am trying to change the datafield in a pivot table using VBA, I was able to add a new field but the old field is not getting removed.

The following code throws an error "Unable to set the Orientation Property of the PivotField Class".

ActiveSheet.PivotTables("PivotTable1").PivotFields("Document ID").Orientation = xlHidden

I churned my brain, but unable to find the reason for this error. Please guide me.

Thanks,
Anil

 

[AnilPullagura]

Just gave this a bump incase if someone missed this...., i did a whole research yet unable to get a resolution to this issue. Help needed!!!!!!!

I use Excel 2007 Standard version

--Thanks

 

[AnilPullagura]

I figured it out...., i am such a Dumbo.., pardon me for wasting ur time.... didnot include "count of".
just to share with you people:


ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Document ID").Orientation = xlHidden</pre>

 

[Razorman]

I figured it out...., i am such a Dumbo.., pardon me for wasting ur time.... didnot include "count of".
just to share with you people:


ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Document ID").Orientation = xlHidden

I'm experiencing the same type of issue. Your post pointed me in the right direction.
However, I'm stil experiencing some issues. The name of the field that I'm trying to hide contains some special characters and I am curious about whether this might be the problem.
The datafield label is “abc abc EXT %”
The source name is “abc_abc_abc_EXT_%”
This is the code I’m trying to run
pt.PivotFields("abc_abc_abc_EXT_%").Orientation = xlHidden
Can anybody spot a reason as to why it would still give me the error “Unable to set the Orientation property of the PivotField class”

 

[RoryA]

Have you tried:

pt.PivotFields("abc abc EXT %").Orientation = xlHidden

Is it a calculated field?

 

[Razorman]

Yes, it’s a calculated field.
In the meantime, I’ve used code to remove all the fields, and then add back the fields that I want.

 pt.DataPivotField.Orientation = xlHidden

 

[Razorman]

Have you tried:

pt.PivotFields("abc abc EXT %").Orientation = xlHidden

Is it a calculated field?

Yes, that is the code that I tried first, and is the same as the Macro Recorder produces.
However, it gives the same error message.
Then when I rename to the Source name, it still gives the same error.

 

[RoryA]

If it's a calculated field you basically need to refer to it as an Item of the Values field:

With ActiveSheet.PivotTables(1).DataFields("abc abc EXT %")
.Parent.PivotItems(.Name).Visible = False
End With

 

[Razorman]

If it's a calculated field you basically need to refer to it as an Item of the Values field:

With ActiveSheet.PivotTables(1).DataFields("abc abc EXT %")
.Parent.PivotItems(.Name).Visible = False
End With

Ok that worked perfectly! I never got round to coming back here to close the loop but remembered now.
Thanks again.

It would have been nice though if the macro recorder knew how to differentiate, instead of recording code which doesn't work.

 

[MicaRunner]

I'm trying something similar but running into and issue--

I'm trying to write some code to add and then remove a calculated field to and from a Pivot Table. Below are the two pieces of code:

Sub AddPivotField()


    With Worksheets(1).PivotTables("PivotTable1")
        .AddDataField Worksheets(1).PivotTables( _
        "PivotTable1").PivotFields("hProdUtil"), "Sum of hProdUtil"
        .DataBodyRange.NumberFormat = "#0.0%"
    End With


End Sub

And:

Sub RemovePivotField()


    With Worksheets(1).PivotTables(1).DataFields("Sum of hProdUtil")
        .Parent.PivotItems(.Name).Visible = False
    End With


End Sub

The AddPivotField works fine but when I run the RemovePivotField I get the "Object doesn't support this property or method" error. Any ideas?


Thanks