Pivot Table
 

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

Pivot Table
 

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.

Pivot Table
 

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
'============================

C. Roenbaugh wrote:
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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html