Copying pivot table

[Nadine]

Hello,

I have made a pivot table containing the ages of the employees in our firm.
I thus have a pivot table with all the ages (ex. 20,21,22 etc) for both men
and women. I would like to group the ages say by tens and have copied the
pivot into a new worksheet. So far so good, but when I now group the ages,
this grouping is also shown in the original pivot table! I already tried
copying only the pivot table and copying the entire worksheet but the result
is the same. Could anyone help me with this please?

 

[Roger Govier]

Hi Nadine

both your PT's are using the same Pivot Cache.
When you create the PT, and selected the same source range for the data, you
must have chosen the option to use the same Pivot Cache to reduce storage
requirements.
Had you chosen not to use the same cache you would have been OK.

I would give the set of data two different names.
Create Dynamic ranges so they will grow if you add more data to the data
set.
Insert>Name>Define> Name myData Refers to >
=$A$1:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))
Repeat but call the second set myData2

(Note: this formula assumes that your header row is row 1 for your pivot
table. You will need to adjust if it is different)

On each of your PT's, right click>PT wizard>Back>Source=myData and myData2
respectively.
Refresh each PT.

 

[Nadine]

Hello Roger,

Since I already use a dynamic range for all my pivot tables, I simply
started the pivot table over again not using the same cache and the problem
is solved! Thanks a lot for your help (second time already!).

nadine