Pivot Table

[C. Roenbaugh]

I am trying to remove all data from a pivot table. The following code
does not work every time. I don't understand why.

Set PT = Worksheets("PT").PivotTables("PT1")

With PT
'clear old items
On Error Resume Next
.PivotFields("Data").Orientation = xlHidden
.RowFields(1).Orientation = xlHidden

'add row field
.AddFields RowFields:="LocationID"
'add the data filed
With .PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
End With

End With

 

[C. Roenbaugh]

More Information:

The program works when there are two items in the "Data" area of the
pivot table, when there is only one, it doesn't work.

 

[Debra Dalgleish]

If you're using Excel 2002 or later version, you can use the following:

'==================
Dim pf As PivotField

With PT
'clear old items
'On Error Resume Next
For Each pf In PT.DataFields
pf.Orientation = xlHidden
Next pf
.RowFields(1).Orientation = xlHidden

'add row field
.AddFields RowFields:="LocationID"
'add the data filed
With .PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
End With

End With
'============================