Pivot Table Clear All Fiels


M

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
 
Ad

Advertisements

R

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
 
D

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
'====================
 
Ad

Advertisements

M

MichaelR

Debra,

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

Best wishes,
Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top