Macro??

Posted by Danny on October 28, 2001 4:13 PM

Hi all,
I have a worksheet with 4 Pivot Tables on it, all of which are used to create charts on another worksheet.
I wonder if there is a way to automatically refresh all of the Pivot Tables without manually refreshing each one
Danny

Posted by Tom Urtis on October 28, 2001 5:18 PM

Danny,

Here's one way to automatically refresh all 4 pivot tables at once on that one sheet.
Right click on the sheet tab, left click on View Code, and paste this macro in the sheet module:

Private Sub Worksheet_Activate()
With ActiveSheet
.PivotTables(1).PivotCache.Refresh
.PivotTables(2).PivotCache.Refresh
.PivotTables(3).PivotCache.Refresh
.PivotTables(4).PivotCache.Refresh
End With
End Sub

Of course you will need to modify the code depending on what you named your pivot tables. The tables will update every time you activate the worksheet.

Hope this helps.

Tom Urtis