Puvot Refresh

[MichaelR]

I'm trying to write a macro to remove all of the fields that are in the
layout area of my pivot table. I found a macro online that did something
similar and I adapted it to look like this:

Sub PivotRefresh()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.Orientation = xlHidden
Next pf
End Sub

The problem, however, is that the macro doesn't always work. I originally
thought that it was because the data items are not named the same way as
their respective fields (i.e. Sum of.../Count of.../etc.) but sometimes the
macro clears the data fields. Sometimes it runs normally and other times the
following error message pops up: "Unable to set the Orientation property of
the PivotField class."

Is there any way that I can make this macro work? Please help.

Thanks,
Michael

 

[Debra Dalgleish]

Instead of pt.PivotFields, try pt.VisibleFields

I'm trying to write a macro to remove all of the fields that are in the
layout area of my pivot table. I found a macro online that did something
similar and I adapted it to look like this:

Sub PivotRefresh()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.Orientation = xlHidden
Next pf
End Sub

The problem, however, is that the macro doesn't always work. I originally
thought that it was because the data items are not named the same way as
their respective fields (i.e. Sum of.../Count of.../etc.) but sometimes the
macro clears the data fields. Sometimes it runs normally and other times the
following error message pops up: "Unable to set the Orientation property of
the PivotField class."

Is there any way that I can make this macro work? Please help.

Thanks,
Michael

 

[MichaelR]

Thank you.

Instead of pt.PivotFields, try pt.VisibleFields



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