Update all pivots with column filter

Hi All,

I am trying to update all pivots with the extra function that it updates the column filter as well. Up till now I have the following code:

PHP Code: 

Sub RefreshAllPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets

        For Each PT In WS.PivotTables
          PT.RefreshTable
          PT.ClearAllFilters
          PT.pivotfiels("Year Week").PivotFilters.Add Type:=xlCaptionIsBetween, Value1:="201501", Value2:="201552"
        Next PT

    Next WS

End Sub 


The problem occurs when I am Clearing all filters ("PT.Clearallfilters") and I am a bit stuck how to solve this problem.

I would be pleased if someone has the solution for this problem.

Thanks in advanced,

Cheers, Jeroen

Hi Harry,

The problem still occurs when the code hits "PT.clearallfilters". Do you have any suggestions how to solve the problem?

Cheers, Jeroen

Attached you can find the file.

Thanks!

I have one follow-up question. In the code you described in the previous comment you execute the new filter for ALL Pivotitems, however is it also possible to filter only the column items instead of all pivotitems? (I do not want to include the row filter as well).

Looking forward to your reply

Hi All,

Hereby the solution:

PHP Code: 

Sub RefreshAllPivotTables()
Dim PT As PivotTable
Dim WS As Worksheet
Dim pi As PivotItem
Application.ScreenUpdating = False
    For Each WS In ThisWorkbook.Worksheets
       For Each PT In WS.PivotTables
          PT.RefreshTable
            For Each pi In PT.PivotFields("year week").PivotItems
               pi.Visible = False
              If pi > "201501" And pi < "201552" Then pi.Visible = True
           Next pi
        Next PT
     Next WS
End Sub 


Thanks for the help!

Cheers, Jeroen