Results 1 to 2 of 2
2003-08-20, 12:35 #1
- Join Date
- Jun 2003
- london, Gtr London, England
- Thanked 0 Times in 0 Posts
Excel Pivot Tables _Refreshing using VBA (english)
When I change the reference to the source data behind an Excel Pivot Table using VBA only the original pivot table is changed. Other pivot tables based on (or linked to) this original pivot table remain the same and are based on the original reference to the data set.
Essentially the following code was used (more details can be found in the attachment): Dim CellsData As Range and Set CellsData = Range("D1").CurrentRegion. I then used: ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=CellsData' to redefine the range. However this only updates one of the pivot tables and not any of the linked ones.
This is different to the manual process in which if the source data is changed ( for example inserting or deleting rows) all pivot tables based on that data are updated when the refresh command is applied to one of the tables.
Is there a way of changing the reference to the dataset used for all linked pivot tables ?
Why do the results of the manual procedure differ to those of the VBA procedure ?
2003-08-20, 17:35 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Excel Pivot Tables _Refreshing using VBA (english)
I am afraid that this is a bug in Excel VBA. There are several questions about this problem in the MS Newsgroups, without a satisfactory solution.
Microsoft gives an example of changing the datasource of a pivot table based on external data in <!mskb=269619>Microsoft Knowledge Base Article 269619<!/mskb>, and remarks at the end:<hr>The previous code may not work as expected if you are using shared PivotCaches (...)So you're out of luck, it seems. Updating the source data breaks the links between the pivot tables. You can verify this by creating a pivot table based on the first one (this isn't the case in the workbook you attached <img src=/S/grin.gif border=0 alt=grin width=15 height=15>). ActiveWorkbook.PivotCaches.Count will increase by 1 after running the code.
If multiple PivotTables on a worksheet are derived from the same PivotTable, the subroutine does not work after it processes the first PivotTable. There is no known workaround for this problem.<hr>