Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 Pivot Tables - Updated Data Source
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").ChangePivot Cache 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 Pivot Tables - Updated Data Source
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. "TopRoper" wrote: 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").ChangePivot Cache 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables - Editing the data source shared by multiple pivot ta | Excel Discussion (Misc queries) | |||
Data Source in Pivot Tables | Charts and Charting in Excel | |||
Excel 2007 change pivot data source | Excel Discussion (Misc queries) | |||
Source Data in Pivot Tables | Excel Worksheet Functions | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions |