Setting the same filter at once for multiple pivots

[zima8172]

Hi!

I am trying to speed up my daily routines at work. Looking for a way for a few dozen same-source pivots to change the value (for example, for the <City> drop-down field) to "NYC" when I set "NYC" value in one of the pivots.

Appreciate your help.

Polar

 

[BrianB]

There are several things to consider here.
1. It is possible to automate the process to run the macro, however this may be more trouble than it is worth depending on how many pivot tables there are and how long it takes to run. So a button may be best.

2. The main problem is that changing a pivot page does not itself trigger any trappable "event", so we have to rely on the Calculate event - which itself will not occur unless something else in the sheet calclulates. This is overcome (if necessary) by putting =NOW() in a cell somewhere to force this event.

3. It may not be desirable to have this run every time the worksheets calculate. The macro operation can be confined to calculate only when the page field is changed. This is achieved by checking the range of the Active Cell - so **It is necessary to select the page field before changing it**. The method requires code in each worksheet module which calls a macro in a normal module. This is the one which could be run via a button.

'===========================================================
'- SHARED ROUTINE TO REFRESH TABLES
'===========================================================
Public SelectedPageField As Variant
'-----------------------------------
'- main macro
Sub RefreshAllTables()
    '- temporarily stop other things happening
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
    '- main loop
    For Each ws In Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
            pt.PivotFields("PERSON").CurrentPage = SelectedPageField
        Next
    Next
    '- restore environment
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub


'=============================================================
'- CODE IN EACH WORKSHEET MODULE
'- right click tab & 'View code'
'- may require =NOW() in a cell somewhere to force calculation
'=============================================================
Private Sub Worksheet_Calculate()
    If ActiveCell.Address = "$B$2" Then ' PageField address
        SelectedPageField = ActiveCell.Value
        RefreshAllTables
    End If
End Sub

 

[zima8172]

Thanks a lot for your help!

Really appreciate it

est regards,
zima8172