Macro for Reseting Filters in Pivot tables

[gameover]

Hi All,

I have a pivot table which consists 49 columns and more than 7000 rows. I have made some check boxes which hide 42 columns and can be seen only after clicking on check boxes. Due to so many columns, User commonly forget in which column he has put a filter, so I need a macro which can directly reset all the filters to original value "All" for hidden and unhidden columns both and does not effect the macros assigned to check boxes i.e. the hidden columns must remain hidden but if there is any filter in those hidden columns that should be reset to its original value i.e. "ALL".
I hope I have cleared my self but still if any one of you have some questions you can ask me.
Please answer my question ASAP. I'll be thankful to all of you.
Thanks in Advance!!!!

 

[gameover]

Hi All,

Thanks for your responses. I got a macro by combining all of your replies which is as follows:

Sub RemovePivotColumnFilters()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each pt In ActiveSheet.PivotTables
With pt
For Each pf In pt.RowFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
'pf.AutoSort xlAscending, pf.SourceName
Next pf
End With
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = 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

Thanks again for your replies!!!!!

 

[gameover]

Hi Again,

I am facing a small problem with above stated code. Whenever I close my sheet and then again open, and rerun the master reset macro then this macro disables all the macros which were present in the sheet and also disables the autofilter. Please help me again!!! What should I do now?

 

[RoryA]

There is nothing in that macro that would disable macros.

 

[gameover]

Yes, I was thinking the same thing but its disabling the macro which are assigned to check boxes due to which the columns are hidden. After running this macro, the hidden columns which ought to have appear after clicking the check boxes are not visible and the autofilter is removed!!! What should I do?????

 

[RoryA]

I have no idea as I can't see your workbook. All I can say is that that code does not unhide or hide columns, manipulate any autofilters or change macro settings, so I can't see why it would have the effect you describe. Have you tried adding a msgbox statement to your checkbox macros just to see if they are being called at all?

 

[gameover]

Hey Rory, I'll try it and then let you know, just leaving now for the meeting to present my sheet!! Hope I get 1 more day, if you get any chance to write a new macro for the same problem then please let me know. Thanks again for all your help.

 

[gameover]

Hi,

Another problem with me, the macro which I wrote is taking nearly 10 mins for resetting the fields, so my BOSS wants me to write some thing so that the macro reset only those columns which do not have the default value "Show All" ! Please help me!!!!!!!!