Pivot Table Clear All Fiels

[MichaelR]

Hi,

I'm trying to write a macro that will return a pivot table to its beginning
format with all fields listed in the PivotTable Field List but without any of
them being selected. The purpose of the macro is for the user to be able
start from scratch in a very easy way. However, I don't want to delete the
PivotTable and then reinsert it because it takes a long time to import the
data from access. Is there any way that I could do something like this:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = _
xlHidden

for all page fields, column fields, row fields and data values (regardless
of their name or how many of them are being used)?

Thanks,
Michael

 

[roni]

Hi,

I'm trying to write a macro that will return a pivot table to its beginning
format with all fields listed in the PivotTable Field List but without anyof
them being selected. The purpose of the macro is for the user to be able
start from scratch in a very easy way. However, I don't want to delete the
PivotTable and then reinsert it because it takes a long time to import the
data from access. Is there any way that I could do something like this:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = _
        xlHidden

for all page fields, column fields, row fields and data values (regardless
of their name or how many of them are being used)?

Thanks,
Michael

Try SiSense. They have easier pivot tables and connect to excel.
http://www.sisense.com

 

[Debra Dalgleish]

You could use something like this:

'====================
Sub ClearPivot()

On Error Resume Next

Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next pf
End With

ActiveWorkbook.ShowPivotTableFieldList = True

End Sub
'====================

 

[MichaelR]

Debra,

Thank you so much for your help. The macro worked wonderfully!

Best wishes,
Michael