Refresh External Data Then All Pivot Tables Based On It

[Dave]

I have a External Data Range on a worksheet ("Sheet1") named "Table1".
I have a Pivot Table on worksheet "Sheet2" named "PivotTable1" and it
was created using the External Data Range "Table1" on "Sheet1". I have
another Pivot Table on "Sheet3" named "PivotTable2" and it was created
using the data from "PivotTable1".

Here's my problem: I want to write a macro that refreshes all three;
the External Data Range and the two Pivot Tables. I tried using
"ActiveWorkbook.RefreshAll", however this apparently refreshes all
three simultaneously. Because "PivotTable1" is based on the External
Data Range, and it's being refreshed at the same time as the External
Data Range, no changes appear in the Pivot Tables.

I guess what I'm looking for is a way to tell Excel to first refresh
the External Data Range "Table1", then to wait until the entire refresh
is complete before refreshing "PivotTable1". Apparently because
"PivotTable2" is based on "PivotTable1", it will automatically be
refreshed when "PivotTable1" is.

Any help would be greatly appreciated.

Thanks,

Dave Brandes
(e-mail address removed)

 

[Tom Ogilvy]

Worksheets("Sheet1").QueryTables(1).Refresh Backgroundquery:=False
Worksheets("Sheet2").PivotTables("PivotTable1").RefreshTable

However, your problem is probably that the QueryTable has the
backgroundquery option set to True - if you change it to False for all
querytables, then the Refreshall will probably work.