+ Reply to Thread
Results 1 to 2 of 2

Changing grouping on one one pivot table affects other pivots - no good

  1. #1
    Registered User
    Join Date
    10-12-2012
    Location
    San Francisco
    MS-Off Ver
    2010
    Posts
    2

    Exclamation Changing grouping on one one pivot table affects other pivots - no good

    I have four pivot tables on four different sheets. when I use VBA to change the grouping on columns on one sheet, it also affects the other sheets. It also does this if I change grouping manually.

    Example:
    Three pivots are grouping columns by month (date columns).
    On the fourth table (on a separate sheet), I use the VBA code:
    With vSheetCtr.Range("B6") 'This points at the "JAN" title.
    If .Value <> "" Then
    .Group Start:=True, End:=True, _
    Periods:=Array(False, False, False, False, False, True, False)
    End If
    End With
    It works fine on that sheet, but it ALSO changes the grouping on two of the three other sheets. And it happens at the ".group" step.

    Anyone? Thanks -
    Last edited by andylists@gmail.com; 01-21-2013 at 12:10 AM.

  2. #2
    Registered User
    Join Date
    10-12-2012
    Location
    San Francisco
    MS-Off Ver
    2010
    Posts
    2

    Re: Changing grouping on one one pivot table affects other pivots - no good

    Replying to my own thread! I found it has to do with the pivotcache - if multiple pivot tables use the same pivot cache, the grouping (since it is associated with the cache, not the pivot) affects all pivot tables accessing the same cache. So - make multiple RANGE names for the source data even if the referenced areas are the same for all names. Then change the datasource for the pivots to use a different range name, and it separates out the groupings! (and other "stuff") Works like a charm.

    Closing the thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1