Macro for Reseting Filters in Pivot tables

Yard

Well-known Member

Joined

Nov 5, 2008

Messages

1,929

• Jan 13, 2009

• #2

Not tested:

Code:

Sub ClearPFFilters()
 
Dim PF as PivotField
Dim PT as PivotTable
Dim WS as Worksheet
 
Set WS = Worksheets("MySheet")
Set PT = WS.PivotTables("MyPivot")
 
For Each PF in PT
      PF.ClearAllFilters
Next PF
 
End Sub

 

Upvote 0

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

43,515

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jan 13, 2009

• #3

I assume that's 2007 code since the ClearAllFilters method doesn't exist in 2003 or prior?

 

Upvote 0

Yard

Well-known Member

Joined

Nov 5, 2008

Messages

1,929

• Jan 13, 2009

• #4

I assume you're right!

 

Upvote 0

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

43,515

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jan 13, 2009

• #5

That's a dangerous assumption to make!

 

Upvote 0

G

gameover

Active Member

Joined

Jan 12, 2009

Messages

292

• Jan 13, 2009

• #6

Hi,

Thanks for the reply, but I am using Excel 2003!! Please help me as I have to present this report in less than 24 hours!!!!

 

Upvote 0

R

Robert E Lee

Active Member

Joined

Aug 10, 2005

Messages

266

• Jan 13, 2009

• #7

Hi

maybe instead of

Code:

For Each PF in PT
      PF.ClearAllFilters
Next PF

this (untested)

Code:

For Each PF in PT
      PF.Visible=True
Next PF

Robert

 

Upvote 0

G

gameover

Active Member

Joined

Jan 12, 2009

Messages

292

• Jan 13, 2009

• #8

Hi Robert,

Its still not working an error message comes up "Subscript out of scope"
What should I do now?

 

Upvote 0

R

Robert E Lee

Active Member

Joined

Aug 10, 2005

Messages

266

• Jan 13, 2009

• #9

It may be that as the element of the Pivot you are dealing with is a PivotItem rather than a PivotField, so, again based on Yard's 2007 code, try

Code:

Sub ClearPFFilters()
 
Dim PI as PivotItem
Dim PT as PivotTable
Dim WS as Worksheet
 
Set WS = Worksheets("MySheet")
Set PT = WS.PivotTables("MyPivot")
 
For Each PI in PT
      PI.Visible = True
Next PI
 
End Sub

Robert

 

Upvote 0

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

43,515

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jan 13, 2009

• #10

Try this:

Code:

Sub RemovePivotColumnFilters()
   Dim pt As PivotTable
   Dim pf As PivotField
   Dim pi As PivotItem
   Set pt = ActiveSheet.PivotTables(1)
   On Error Resume Next
   pt.ManualUpdate = True
   For Each pf In pt.ColumnFields
      For Each pi In pf.HiddenItems
         pi.visible = True
      Next pi
   Next pf
   pt.ManualUpdate = False
End Sub

 

Upvote 0