Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    london, Gtr London, England
    Posts
    13
    Thanks
    0
    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 (...)

    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>
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •