Thread: 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

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.

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