Data Fields in Pivot Table

I'm trying to write a macro that will clear all fields in my pivot table
including the data fields. I have the following:

On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField

Sheets(4).PivotTables("PivotTable2").ClearAllFilters
Set pt = Sheets(4).PivotTables(2)
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next pf
End With

The macro above clears all fields except the data fields. Any ideas for what
I could add to the macro to make it also clear the data fields?

Thanks.

Debra
Tue Jul 22 14:26:55 CDT 2008

Your code works for me. Are all the data fields left in the layout?

MichaelR wrote:
> I'm trying to write a macro that will clear all fields in my pivot table
> including the data fields. I have the following:
>
> On Error Resume Next
> Dim pt As PivotTable
> Dim pf As PivotField
>
> Sheets(4).PivotTables("PivotTable2").ClearAllFilters
> Set pt = Sheets(4).PivotTables(2)
> With pt
> For Each pf In .VisibleFields
> pf.Orientation = xlHidden
> Next pf
> End With
>
> The macro above clears all fields except the data fields. Any ideas for what
> I could add to the macro to make it also clear the data fields?
>
> Thanks.


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

MichaelR
Thu Jul 24 13:07:00 CDT 2008

I just realized that the problem wasn't in the macro but in fact in the pivot
table name. When I changed pivottables(2) to pivottable("PivotTable2") it
worked. Thanks again.

"Debra Dalgleish" wrote:

> Your code works for me. Are all the data fields left in the layout?
>
> MichaelR wrote:
> > I'm trying to write a macro that will clear all fields in my pivot table
> > including the data fields. I have the following:
> >
> > On Error Resume Next
> > Dim pt As PivotTable
> > Dim pf As PivotField
> >
> > Sheets(4).PivotTables("PivotTable2").ClearAllFilters
> > Set pt = Sheets(4).PivotTables(2)
> > With pt
> > For Each pf In .VisibleFields
> > pf.Orientation = xlHidden
> > Next pf
> > End With
> >
> > The macro above clears all fields except the data fields. Any ideas for what
> > I could add to the macro to make it also clear the data fields?
> >
> > Thanks.
>
>
> --
> Debra Dalgleish
> Contextures
> www.contextures.com/tiptech.html
> Blog: http://blog.contextures.com
>
>