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 !
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