Pivot 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 above cycles through each pivot
field in the pivot field list even though the data items are not named the
same way as their respective fields (i.e. Sum of.../Count of.../etc.). The
result is that the macro gives an error message when it tries to remove the
field that is in the data area.

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

Thanks,
Michael

 

[Nayab]

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 above cycles through each pivot
field in the pivot field list even though the data items are not named the
same way as their respective fields (i.e. Sum of.../Count of.../etc.). The
result is that the macro gives an error message when it tries to remove the
field that is in the data area.

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

Thanks,
Michael

Hi Michael,
what is the error message?

 

[MichaelR]

The error message says: "Unable to set the orientation property of the
PivotField Class"

Sometimes the macro actually clears all fields (including the data fields)
and sometimes this message pops up and the field in the data item will still
be there.