macro to refresh pivot table

[ericlch16]

I have one workbook which contains about 10 sheets.

most of the sheets have pivot table in it. some of them has two pivot table next to each other.

i need a macro which will refresh all pivot table when the new data is pasted in the data sheet.

Also there is a filter for each pivot table which is called "centre" which can have values a, b, c, d, e. I want the macro to select "a" for all pivots in that workbook.



Any idea?

 

[Craigjre]

I've not tried this code but are you looking at something like this:

Dim PT as PivotTable
Dim WS as Worksheet

Sub test()

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each PT in WS.PivotTables
PT.PivotCache.Refresh
Next PT
Next WS

End Sub

 

[ericlch16]

how to make sure a pivotfield("centre") is a for all pivot table?

 

[Craigjre]

Hey, try this:

Dim PT as PivotTable
Dim WS as Worksheet

Sub test()

For Each WS In ThisWorkbook.Worksheets
For Each PT in WS.PivotTables
PT.PivotCache.Refresh
PT.Pivotfields("Centre").CurrentPage="A"
Next PT
Next WS

End Sub

let me know if that does what you want it to