Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi,

    I have 3 pivot tables (I copied the 1st pivot table, and pasted it to make the 2 new pivot tables). When I group items on 1 pivot table, it makes the same groupings on the other 2 pivot tables. Somehow these are linked and I'd like to "unlink" them. Is this possible? I tried renaming the pivot tables from PivotTable1 to LANA, and the link is still there. Any help is appreciated, as always!

    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of copying and pasting the pivot table, create a new one from scratch:
    - Click anywhere in the source table, or select the source table.
    - Activate the Insert tab of the ribbon.
    - Click Pivot Table in the Tables section.
    - The range should already be entered in the Table/Range box. Click Next >.
    - Excel will ask you whether you want to base the new pivot table on an existing one.
    - IMPORTANT: answer No here. This will make the new pivot table independent of the old one.
    - Continue as usual.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I was afraid that would be the answer... I was trying to avoid making one from scratch and thought there might be a way to "unlink" it without starting over. Oh well.
    Thanks for answering so quickly Hans!
    Lana

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You may be able to simply step back through the wizard and change the data source that way?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Thanks for the info.... I had already changed the data source, and this indeed did "unlink" the pivot tables, however when I changed the data source back to the original, the link remained. I thought I could "trick" the pivot table to "unlink" by doing this... guess not!!!

    Thanks for your reponse Rory!
    Lana

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you change the CacheIndex of the Pivot table?

    For example:

    ActiveSheet.PivotTables(1).CacheIndex = 2

    When they are linked they all have the same cacheIndex, ones created independently do not...

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Do I have to run a macro to change this cache index, or do I somehow change this cache index in some type of "properties" area? I couldn't see the cache anywhere, so I must be looking in the wrong place.
    Thanks!
    Lana

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could copy/paste Steve's line into the Immediate window in the Visual Basic Editor and press Enter. But I tested it in Excel 2002, and it didn't work - I got an error message (which was to be expected, but it was worth a try).

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I put the line into VBA's immediate window and pressed enter and it worked (no error message) when I left it as a "1", but then got an error message when I changed the cache to a "2".... because I didn't get an error message with the "1", I'm thinking he's on to something?!?!?!

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You didn't get an error with the 1 because you didn't change anything. There is only one pivot cache, so you just pointed the pivot table at the one it's already using; when you try 2, you are trying to point it to a cache that does not exist, hence the error.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Behind the scenes, Excel creates something called a pivot cache when you create a pivot table. The pivot cache is an invisible intermediate data store that is used to speed up displaying the pivot table. The pivot table is based on the pivot cache, and the pivot cache is based on the source data.

    When you create a second pivot table based on the same data, Excel asks you whether you want to base it on the the first one.

    If you answer Yes, Excel actually bases the second pivot table on the existing pivot cache. This also happens if you copy/paste a pivot table.

    If you answer No, Excel creates a second pivot cache and bases the new pivot table on it.

    The line

    ActiveSheet.PivotTables(1).CacheIndex = 2

    tells Excel to base the pivot table on the second pivot cache of the worksheet. But if you have never answered No in the dialog mentioned above, there is no second pivot cache, so the line causes an error.

    Sorry if this sounds very technical!

Posting Permissions

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