Refresh External Data Then All Pivot Tables Based On It
 

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

Refresh External Data Then All Pivot Tables Based On It
 

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

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.

--
Regards,
Tom Ogilvy

"Dave" wrote in message
oups.com...
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