Excel 2007 Pivot Tables - Updated Data Source

T

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?
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to update pivot table data range 3
Upadte PivotTable Data range 2
Format Pivot total rows 2
Using macro to switch 'page' in a pivot table? 1
syntax in VBA to create a pivot table 2
Update Pivot Table Filters based on a Cell Value in Excel 2007 0
VBA Filtered Pivot Table Showing "Multiple Values Selected"; Only One Value Selected 0
Macro works on some but not for all 0

Top