Skip to Content
0
Jul 31, 2014 at 11:30 AM

How to automatically refresh Pivot tables after query refresh

96 Views

Hi all,

I have been trying to find this answer online for a while now with no success.

We are on BI 7.0 and I have a workbook with one query and three Pivot tables.

I am trying to find a way, on opening of the workbook (it is set to refresh workbook on open)

to then automatically refresh the Pivot tables using the following macro;

Sub RefreshAllPivots()

Dim PC As PivotCache

For Each PC In ActiveWorkbook.PivotCaches

PC.Refresh

Next PC

End Sub

On looking, this seemed possible in 3.5 by enhancing SAPBEXonRefresh, however it doesn't seem to be as straight forward in 7.0.


I have found some stuff on Callback being mentioned etc but no clear and definitive procedure how to implement.

Any help is greatly appreciated and points will be awarded.


Many thanks,

Shane.