Update Pivot Tables - VBA

[nichola.hartley]

Hi. I have multiple pivot tables in multiple worksheets - on each worksheet I have a source pivot table (OLAP cube) and 4 pivots that work off this data.

In my macro I go through each worksheet, change the necessary olap cube fields and then i want to refresh all the pivot tables in that sheet - i do not however want to refresh all the workbook.

I can only seem to do this by refreshing one specific pivot table eg
'ActiveSheet.PivotTables("ForecastSourcePivot").PivotCache.Refresh'

OR refresh all in the workbook eg
'ActiveWorkbook.RefreshAll'

Is there a way to do each sheet individually?!

Thanks!

 

[Misca]

Try:

Dim PT As PivotTable
For Each PT In ActiveSheet.PivotTables
    PT.PivotCache.Refresh
Next PT