Excel 2007 Pivot Tables - Updated Data Source

[TopRoper]

We have just upgraded from Excel 2002 to 2007 and have found that VBA
associated with pivot tables behaves differently.

In Excel 2002 all we needed to do was ...

Sheets("Rev").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

N.B. The data source is refreshed daily and is constantly extending.

So in Excel 2007 we seem to need to 'manually' update the data source before
refreshing the pivot table.

So far I have failed to get anything to work fully.

Here's an extract of the code so far ...

Dim rgSource As Range

Windows("Data.xls").Activate
Sheets("RawData").Select
Range("C1").CurrentRegion.Select
Set rgSource = Selection
Windows("Pivot.xls").Activate
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=rgSource, _
Version:=xlPivotTableVersion10)

It all goes well up to the last statement !

So, essentially I want to update the data source in a pivot table. The
Pivot tale is in Pivot.xls and the data, which is updated daily, is in
Data.xls.

Anyone out there who can help me out?

 

[TopRoper]

We now have a solution ... see "updating pivot table to include additional
rows" thread.

Happy to receive any suggestions on a more elegant solution !!

Thanks.