Update Pivot Field Using Macro

[CBROWN1]

Hi All-

I want to update the selected fields in multiple pivot tables based on cell contents but can't seem to get the macro right.

So for example one of the fields is "Year". I want the pivot tables to be limited to data that matches the value held in Sheet 1 Cell A1 (e.g. 2010).

Is there a simple piece of code that will do this?

Thanks in advance,
Cat

 

[Jerry Sullivan]

Hi Cat,

This post might help.

http://www.mrexcel.com/forum/showthread.php?t=583204

If you copy the Filter_PivotField function shown in that thread into the same VBA module with the Sub below,
it should do what you describe.

Edit MyPivotTableSheet and MyPivotTable1 to match your names.

Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("MyPivotTableSheet") _
            .PivotTables("MyPivotTable1").PivotFields("Year"), _
            varItemList:=Application.Transpose(Sheets("Sheet1").Range("A1"))
End Sub

 

[CBROWN1]

Perfect....many thanks!