none
Excel PivotTable Grouping: VBA Grouping (Month, Year) command changes Grouping of previous Pivot Tables created in same macro RRS feed

  • Question

  • Excel 2010;  Windows 7 both up-to-date

    Three PivotTables are prepared in one macro.

    Everything worked fine until I attempted to rewrite the code more efficiently.

    Originally, my code created three separate PivotCaches with the following code: (Notice the Version references which were removed for version 2)

       Dim PTcache As PivotCache
       Dim PT1 As PivotTable
       Dim PT2 As PivotTable
       Dim PT3 As PivotTable

       NOTE: "PivotData" is a Named Range

    Original Code:

       ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
           "PivotData", VERSION:=xlPivotTableVersion14).CreatePivotTable _
           TableDestination:="'By Month'!R3C1", _
           TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
       With ActiveSheet.PivotTables("PivotTable1").PivotFields("Paid Date")
           .Orientation = xlRowField
           .Position = 1
       End With
       Range("A4").Select
       Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
       With ActiveSheet.PivotTables("PivotTable1").PivotFields("Years")
           .Orientation = xlRowField
           .Position = 2
       End With

      The only original differences for PivotTable2 and PivotTable3 was
       ... TableName:="PivotTable1" ... became
       ... TableName:="PivotTable2" ...
       ... TableName:="PivotTable3" ...  respectively and,

       ... TableDestination:="'By Month1'!R3C1" ... became
       ... TableDestination:="'By Month2'!R3C1" ...
       ... TableDestination:="'By Month3'!R3C1" ... respectively

    Revised Code:        'Notice: VERSION:=xlPivotTableVersion14) is removed. Could this be an issue?
     
       ' Create a Pivot Cache
       Set PTcache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="PivotData")    
       ' Add new worksheet
       Sheets.Add.Name = "By Month"
       ' Create the Pivot Table from the Cache
       Set PT1 = ActiveSheet.PivotTables.Add(PivotCache:=PTcache, TableDestination:="'By Month1'!R3C1", TableName:="PT1")

       PT1.PivotFields("Paid Date").Orientation = xlRowField

       Range("A4").Select
       Selection.Group Start:=True, End:=40908, Periods:=Array(False, False, False, False, True, False, True)
       PT1.AddDataField PT1.PivotFields("Amount"), "Sum of Amount", xlSum
       'Add/Rename other Fields
       With PT1
          .PivotFields("Years").Orientation = xlColumnField
          .CompactLayoutRowHeader = "Month"
          .DataPivotField.PivotItems("Sum of Amount").Caption = "Expense Amount"
          .CompactLayoutColumnHeader = "Year"
       End With

       For the two other Pivot Tables:       'Notice ... PivotCache:=PTcache ... is the same for all three tables; they were separate above

       Set PT2 = ActiveSheet.PivotTables.Add(PivotCache:=PTcache, TableDestination:="'By Month2'!R3C1", TableName:="PT2")
       Set PT3 = ActiveSheet.PivotTables.Add(PivotCache:=PTcache, TableDestination:="'By Month3'!R3C1", TableName:="PT3")

    When the revised code is run, the moment that the following line is run, any previously created PivotTables change its display:
       Selection.Group Start:=True, End:=40908, Periods:=Array(False, False, False, False, True, False, True)

    It seems that the above code line modifies any previous table created with the similar Group By.

    Is there a way to create three separate "Group By" objects for the three separate PivotTables PT1, PT2, & PT3?
    or is there another issue I am missing?

    TIA Dennis

     

    Tuesday, November 8, 2011 5:16 PM

Answers

  • Dennis,

    If you create multiple pivot table from one cache, they all group the data similarly. You need to create additional caches if you want them to be treated separately.

    "Everything worked fine until I attempted to rewrite the code more efficiently."

    I would suggest that there are few inefficiencies if the code actually works ;-)

    Bernie


    HTH, Bernie
    • Marked as answer by DennisCPA Wednesday, November 9, 2011 8:31 PM
    Wednesday, November 9, 2011 2:30 PM

All replies

  • Dennis,

    If you create multiple pivot table from one cache, they all group the data similarly. You need to create additional caches if you want them to be treated separately.

    "Everything worked fine until I attempted to rewrite the code more efficiently."

    I would suggest that there are few inefficiencies if the code actually works ;-)

    Bernie


    HTH, Bernie
    • Marked as answer by DennisCPA Wednesday, November 9, 2011 8:31 PM
    Wednesday, November 9, 2011 2:30 PM
  • This was my 1st attempt to automate a PT.

    In the interim, I did also make the assumption that I may not be able to use one cache three ways.  Sill, I held out thinking there nay still be another solution.

    Thanks for confirming my issue and for being a major player over the years!

    Dennis

     

    Wednesday, November 9, 2011 8:31 PM