Autoupdate Multi Pivot Tables complex filters

[Emad]

At the outset, well done chandoo .. excellent work and brilliant ideas ..

Am running multi pivot tables (excel 2007) with almost similar criteria filters (e.g Month for all of them on monthly basis) .. Is there a way to run a macro (or else )that would auto change the criteria filters for all of them at once ? (e.g enter OCT for the Month filter for 30 P.T at once, etc !)

Best regards and keep up the good work

Emad

 

[Luke M]

Maybe something like this would work? Needs a few adjustments before it will work correctly, but should get you started. Right now, it's set so that you select a cell with the value you want all the Month values changed to.

[pre]

Sub UpdateAll()
Dim NewValue As String
Dim ws As Worksheet

NewValue = ActiveCell.Value
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
'This line may be tricky...need to know which field
'setting you are changing.
'Is it a page, row, column, etc.
'Record yourself changing the PivotTable to see
'the correct coding to use
pt.PivotFields("Month").CurrentPage = NewValue
Next pt
Next ws

End Sub

[/pre]
You might be able to combine this with some sort of event macro, but didn't want to get too far w/o knowing that this will work.

 

[Emad]

Thanks Luke .. appreciate some details to use your module, plz explain further as this is too advanced for me.

Rgds.

Emad