Updating Pivot Tables

Posted by John on March 23, 2001 6:50 AM

Please could someone put me out of my misery, and tell me if there is a simple bit of VBA to update all pivot tables in a workbook without including any explicit reference in the code to which sheets contain pivot tables, and what the pivot tables are called?

Thanks in anticipation

John

Posted by David Hawley on March 23, 2001 7:00 PM

Hi John

Yes there is! there are a number of ways to do this but in your case, probablt the PivotCache is needed. Try this

Sub UpDateAllPivots()
Dim i As Integer
For i = 1 To ThisWorkbook.PivotCaches.Count
ThisWorkbook.PivotCaches(i).Refresh
Next
End Sub

Dave

OzGrid Business Applications

Posted by John on March 26, 2001 5:31 AM

Dave,

Thanks very much for your help.

Looks like a good tactic, will give it a try.

I hope this tip helps a few other people too. I've certainly picked up a lot just by reading replies to questions on this forum.