A Fix - Pivottable - Added calculated fields and now can't clear pivot

[Andreww]

Hi - I had a problem with my code (see prev posting this morning
"Pivottable - Added calculated fields and now can't clear pivot")

The problem being that I could not set all elements of the pivot to
xlHidden when one of the elements was a calculated field.

I did a lot of searching to try and find a solution, but most of what
I found was other people with the same problem.

A colleague came up with the following idea:

Add a dummy element to the data area of the pivot and then actual make
the pivot data element itself invisible.

It seems for this to work there needs to be at least 2 dimensions/vars
in the pivot data area.

This is the code that (for the moment!) works:

Sub Macro2()
' Adds in a dummy field to the xldatafield of the pivot table
' then the entire data item can be hidden - doesn't seem to work
without
' doing it this way
Sheets("pivot").PivotTables(1).PivotFields("dummy").Orientation =
xlDataField
Sheets("pivot").PivotTables(1).PivotFields("dummy").Orientation =
xlDataField

Sheets("pivot").PivotTables("PivotTable1").PivotFields("data").Orientation
= xlHidden

' Then just hide any elements which are hidden
Set PT = Sheets("pivot").PivotTables(1)

With PT
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next
End With

End Sub

While this may be a fudge of sorts:

a) It works
b) Neither of us could see, or find any information as to why
calculated fields couldn't be hidden.

Hope this helps someone.

Andrew