Auto Refresh Pivot Table after Source Data is Refreshed

[krysolov]

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 here, was to do this:

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:

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

 

[SkipVought]

Hi,

here is code to 1) refresh Table1 and 2) refresh the PivotTable

dim pc as PivotCache

Worksheets("Table1SheetName").QueryTables.Refresh False

for each pc in ActiveWorkbook.PivotCaches
  pc.refresh
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops...

dim qt as QueryTable, pc as PivotCache

for each qt in Worksheets("Table1SheetName").QueryTables
  qt.Refresh False
next
for each pc in ActiveWorkbook.PivotCaches
  pc.refresh
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[krysolov]

Skip,
Thanks for the code! I'm assuming this would have to be added as a macro or on the Worksheet.Activate or Workbook.Open events. I would like to avoid any of those, since placing another macroable button would interfere with how the old spreadsheet that I'm trying to replace used to look like and the users who would have to use it are very... shall we say "stubborn".

The idea behind the process is that the user will have the file opened throughout the day and periodically refresh it to capture the change in inventory balances and open order statuses to see how much they can include in early shipments or on the contrary, move to later days. The sheet containing the data would be hidden, so the user would be required to click the "Refresh All" button on the Data tab. Because of this the Activate and Open events would not work very well and that's why I was trying to find a way to capture when the refresh occurs, so I can insert the Pivot Table refresh code.

 

[SkipVought]

The user should not be required to click anything. Run in the Workbook_Open event, if that event makes sense.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Workbook open event will only fire when they open it. If they keep the workbook open throughout the day then maybe ontime?

thread68-1590040

Gavin

 

[SkipVought]

Yes run in open event and with a timer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[krysolov]

No, timer definitely won't work. The data is relatively fast-changing and should be accessible "on-demand". So making a timer, say, for every hour is definitely too slow, but every 5 minutes would probably be an overkill, especially if it fires off while they are doing the analysis within the spreadsheet. One of the sheets has a lot of "what if" formulas and VB code, but it's based on some of the additional information that they'd be entering. So I dread to think if a timed event executed and modified the result as they are working on it.

So far it seems that the only way to create a true "on-demand" refresh is to create a button on the sheet where the user(s) enter the pickable through date and tie a macro to execute first the query refresh and then the pivot table refresh. I was just hoping that since the users here are familiar with the "Refresh All" button on the Data tab to tie somehow the pivot table to whichever event gets executed by pressing that button, but I guess I'm SOL.

Thanks for all the good suggestions!

 

[Gavona]

Actually you could change the action of the "Refresh All" button on the Data tab by assigning your VBA macro to it.

You would want to use events to apply/dissapply that change so it was only effective when your workbook was active

Easier / more robust to go for a button on your worksheet.

Gavin