How to clear all pivot fields through multiple pivots

lamina123

New Member
Joined
Jan 6, 2016
Messages
1
Hi everyone, wondering if you could help with the following:

I have 1 excel with multiple tabs and 1 pivot table by tab.

I took from here a code really useful and time-saver to clear all filters from one pivot field.

The thing is that this code needs to have each pivot field defined by its particular name. In this example "Org. Level 4".

I would like to expand particular pivot field "Org. Level 4" to every pivot field in each pivot table.

I mean, i have several pivot fields in each pivot, and i would like to be sure that i'm clearing all them out.

One not efficient option i think could be to repeat this same code as many times as pivot fields i have: one code to clear "Org. level 4", another to clear "Org. level 5" and so on till clearing "Org. Level n".

if you have any better or simpler way to do this, will be highly welcomed!

Thanks in advanced !

Code:
Sub ClearAllFilters() 
Dim myWS As Excel.Worksheet
Dim aWB As Excel.Workbook
Dim myPivot As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Set aWB = ActiveWorkbook


Application.ScreenUpdating = False
Application.Calculation = xlManual


'//////////////ORG. LEVEL 4///////////////////////
For Each myWS In aWB.Worksheets  
    For Each myPivot In myWS.PivotTables 
        Set myPivotField = Nothing
        On Error Resume Next
        Set myPivotField = myPivot.PivotFields("Org. Level 4") '--> Here is the key question: i would like to replace this manually-entered pivot field name by a formula or something that allows me to Clear all the pivot fields.  
        On Error GoTo 0
        If Not myPivotField Is Nothing Then
            myPivotField.ClearAllFilters  
            
        End If
    Next myPivot
Next myWS
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top