Excel 2007 Pivot Table Question : Grouping

[bradjedis]

Greetings!

I have a file where there are 2 pivot tables for the same data range. When I created the 2nd pivot table it appears that excel used the same table name.

My issue is that I need to group the data differently on the 2 tables. The grouping is on the first table for a weekly grouping. The second is for monthly. The problem is in that when I group on one of the tables it un-does the grouping on the other table.

Any thoughts? I can post a file but it will take a little while to sanitize the data. (you know, propriatary etc...)


Thanks,
Brad

[HansV]

When you create a new pivot table from the same data as an existing one, you'll be prompted whether you want to base the new one on the existing one. If you answer No, the pivot tables will behave independently of each other.
The dialog below is for Excel 2003, I assume it's similar for Excel 2007.

x33.png

[bradjedis]

That is what I expected, but 2007 did not ask. Pephaps it is a setting somewhere?

Brad

[bradjedis]

Found a solution. Not sure if it is the Best, but it works.

I set 2 Named ranges. same range differnt names. Then adjusted the data range for each pivot table to point at the specific named range.

Brad

[HansV]

You're correct. Apparently it is not possible to specify this in the interface of Excel 2007 any more - another example of loss of functionality.

You've found a relatively simple workaround. Another one is to use code to create a new pivot cache, and use this for a new pivot table that will behave independently of others, for example:

Dim wsh As Worksheet
Dim pvc As PivotCache
Dim pvt As PivotTable
Set wsh = ActiveSheet
Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsh.Range("A1:D20"))
Set pvt = pvc.CreatePivotTable(TableDestination:=wsh.Range("G52"))

You'd still have to add the row fields, column fields and value fields.