Auto Refresh Pivot Table after Source Data is Refreshed

K

krysolov

Joined
Feb 9, 2010
Messages
1
Reaction score
0
Excel 2007.
Table1 in Sheet1 is linked via ODBC to an external data source. PivotTable2 in Sheet2 is based on Table1.

I'd like to have PivotTable2 automatically refreshed when the user refreshes the data through "Data/Refresh All"

My first attempt, based on the archives in Mr. Excel forums, was to do this:
Code: 
Sheet1 Code:
  Private Sub Worksheet_Change(ByVal Target As Range)
  Sheets("Sheet2").PivotTables(1).RefreshTable
  End Sub
That does not seem to work, because the event does not get executed when the data from the external query is updated.

Searching this and other forums, I found the following:
Code: 
Sheet1 Code:
  Private Sub Worksheet_Calculate() 
  Dim pt As PivotTable      
  Application.EnableEvents = False 
  For Each pt In Sheets("Sheet2").PivotTables: pt.PivotCache.Refresh: Next pt 
  Application.EnableEvents = True 
  End Sub
With the same result. The event does not get executed.

Frustrated, I started looking at QueryTable.AfterRefresh event, but that seems to be overcomplicating the matter.

So is there a way to have the Pivot Table automatically refreshed after the based-on data was refreshed by some/any means?

Thank you,
sg
 
Back
Top