Pivot Table Clear All Fiels
 

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

Pivot Table Clear All Fiels
 

On Jun 23, 6:07*pm, MichaelR
wrote:
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

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

Pivot Table Clear All Fiels
 

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


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

Pivot Table Clear All Fiels
 

Debra,

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

Best wishes,
Michael