Help vba disable filters on all pivot tables

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hi people,

Can anyone help please?

i Want to disable filters on all pivot tables of a sheet, but i get error.

Thank you

VBA Code:
Sub DesativaFiltroPivotTables()

Dim pt As PivotTable
Dim ptall As PivotTables
Dim pf As PivotField

Set ptall = ActiveSheet.PivotTables

For Each pt In ptall
      
pf.EnableItemSelection = False
    
Next pt
  
  
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,773
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
Sub filterPT()
  Dim pt As PivotTable
  For Each pt In ActiveSheet.PivotTables
    pt.ClearAllFilters
  Next
End Sub
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Try this:

VBA Code:
Sub filterPT()
  Dim pt As PivotTable
  For Each pt In ActiveSheet.PivotTables
    pt.ClearAllFilters
  Next
End Sub

Hi Dante, Thank you, but i don't clear filters, i want to disable filter selecion, so thar users can't change filter.

I tried this, but doesn't work:

VBA Code:
Sub filterPT()
  Dim pt As PivotTable
  Dim pf As PivotField
  For Each pt In ActiveSheet.PivotTables

    pf.EnableItemSelection = False
   
  Next
End Sub




Can you help, please? Thank you
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hi Dante, Thank you, but i don't clear filters, i want to disable filter selecion, so thar users can't change filter.

I tried this, but doesn't work:

VBA Code:
Sub filterPT()
  Dim pt As PivotTable
  Dim pf As PivotField
  For Each pt In ActiveSheet.PivotTables

    pf.EnableItemSelection = False
 
  Next
End Sub




Can you help, please? Thank you
Hi Dante,

I discovered the solution to my problem:

VBA Code:
Sub DesativaFiltroPivotTables()
Dim pt As PivotTable
Dim pf As PivotField

  For Each pt In ActiveSheet.PivotTables
    For Each pf In pt.PivotFields
      pf.EnableItemSelection = False
    Next pf
  Next
End Sub


Thank you
 
Solution

Forum statistics

Threads
1,144,610
Messages
5,725,297
Members
422,607
Latest member
joce

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
Top