CODEWITHSUNDEEP
vbaexcelpivot-table
drLecter
drLecter

Reputation: 197

Filter every pivot in a workbook on a given value

I am trying to work with filters of pivot tables and not use individual pivot tables.

Ideally I want part of my macro to cycle through all pivots on all sheets, unfilter them and then filter on a date that is already pregiven in a cell.

Here is what I tried:

Dim LastWeek As Date
LastWeek = Range("K4").Value

Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.PivotFields("Week Ending Date(Cal)").ClearAllFilters
pt.PivotFields("Week Ending Date(Cal)").CurrentPage = _
    LastWeek
Next pt
Next ws


ActiveWorkbook.RefreshAll

Tried different things with those two lines that should unfilter and filter on last date. No error comes out. LastWeek variable gets the proper date value, and the date is available.

The loop code goes a couple of times meaning it probably picks up the pivots, but no filtering happens. I also tried using pagerange reference from peltier: http://peltiertech.com/referencing-pivot-table-ranges-in-vba/

I have a couple of filters but want to filter on the 1st one called "Week Ending Date(Cal)"

Could anyone throw in their two cents, please? I havent been able to find a final solution after days of googling.

I could do a recorded macro that would go to each pivot individually but I would like to learn how to do it with loop.

Thanks

Upvotes: 1

Views: 695

Answers (1)

drLecter
drLecter

Reputation: 197

I am using Excel 2010 at the moment but soon will transition to 365 64bit. Hopefully stuff will continue to work.

Thank you both for a prompt reply and for a good idea. I used slicer and it is actually more elegant solution. Id still like to find out how to do it with looping as well for learning purposes.

This is the solution if anyone else needs it:

ActiveWorkbook.RefreshAll
'Refreshed all so that new dates appear in the slicer
Dim LastWeek As String
'Had to restate LastWeek as a String instead of Date
LastWeek = Range("K4").Value


'Week Ending Date (Cal) is the name of the slicer connected with a given filter
'Change this name to fit your purposes and dont forget to connect your slicer with all the pivots
ActiveSheet.Shapes.Range(Array("Week Ending Date(Cal)")).Select
With ActiveWorkbook.SlicerCaches("Slicer_Week_Ending_Date_Cal")
    .SlicerItems(LastWeek).Selected = True
    .SlicerItems("(blank)").Selected = False
End With
ActiveWorkbook.RefreshAll

And later in macro I removed both date reference cells and slicers to make it cleaner and saved as another file in an output folder.

Thanks to everybody.

Upvotes: 1

Related Questions