Results 1 to 3 of 3
  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 - Changing the source data (English/Excel/2000)

    I wonder if you can help.

    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.

    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 ?

    Regards
    Tim

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel Pivot Tables - Changing the source data (English/Excel/2000)

    Not a direct reply to your question, but have you thought about using a named range for your data and just redefining it. This would presumably update all pivot charts that were based on that named range.

    StuartR

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    london, Gtr London, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Pivot Tables - Changing the source data (English/Excel/2000)

    Thanks for the reply.

    I thought that I had effectively used a named range (the code can be found in the original attachment) with the following code: 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.

    Again, 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 ?

    Furthermore, I would be interested to know if there was any method apart from the 'Pivot table Wizard' command that could be used to redefine the source data for a pivot table, without having to recreate the table from scratch. Note that the source data property relating PivotTable object is read-only.

    The attachment contains the relevant VBA code together with pivot tables which relate to the code.

    Regards
    Tim

Posting Permissions

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