Hi everyone,
Hoping someone out there can help me.
I have built a worksheet that has several pivots across a few tabs all with the same data source reading from one tab (raw data). I am wanting to create a button that once clicked, refreshes all pivot tables on the worksheet (about 6 in total across 3 different tabs) whilst retaining the same filters (Yes/No) on each pivot that's applied.
I managed to figure out the refresh part which I found by googling which is working great (see below) but not the part that forces each of the pivots back into the same filter.
####################################
Sub RefreshAllPivots()
Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
[Instructions!A10] = "The pivots were last refreshed at " & Now
End Sub
####################################
The reason I need the pivots to force the filters back is sometimes they break after a refresh. So basically, the raw data has a bunch of Yes/No columns and each Pivot only contains one Yes/No filter. However, sometimes the raw data may contain not contain a 'Yes' which is acceptable but when the pivot is refreshed, it loses that filter going forward. So when adding new data and 'Yes' is contained in that column, the pivot no longer applies 'Yes' since it wasn't previous in the data and filter.
So basically, I just need the Macro to refresh each pivot then apply 'Yes' in as the filter in each of the 6 pivots.
Hopefully that makes but if any further clarification required, please let me know.
Appreciate if anyone can help with this.
Kind regards,
Darren
