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
dave_brandes@msn.com

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.

--
Regards,
Tom Ogilvy

"Dave" <dave_brandes@msn.com> wrote in message
news:1135191073.255348.147770@g49g2000cwa.googlegroups.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
> dave_brandes@msn.com
>