using variable to select filter in pivot

[TheMadTree]

Good afternoon,

I'm trying to use a variable to select filter in a pivot. The code i'm working with is:

""
Workbooks("Banana - Copy.xlsx").Sheets("PIVOT").PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "(All)"
With Workbooks("Banana - Copy.xlsx").Sheets("PIVOT").PivotTables("PivotTable1").PivotFields("Week")
.ClearAllFilters
.PivotItems(" & Weeknumber & ").Visible = True
End With
""

The code stops on "".PivotItems(" & Weeknumber & ").Visible = True""
Why doesn't this work?
Can anyone help me?

 

[TheMadTree]

Good afternoon all,

Found the solution to my problem:
If you have a variant that you want to use as filter in a pivot table, you need to clear all the filters first and apply the filter using the code below.

With Workbooks("Banana - Copy.xlsx").Sheets("PIVOT").PivotTables("PivotTable1")
.PivotFields("week").ClearAllFilters
.PivotFields("Week").CurrentPage = Weeknumber

End With

Hope this helps