Five (equal) Pivottable to be updated in one operation

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi.
(Excel 2007)

I have five pivottables. The first is copied to other sheets. The
tables are now shoving results from different departmens.

All tables have "Year" and "Month" as criterias.

I am looking for a way to change all talbes in one operation e.g.
instead of changing five times I will only have to do it one.

My pivottables are showing two datacolumns. From time to time I want
to change the way the data are displayed. First I want them shown as
regular figures, then I want them shown as % of column. Is there a way
to change all datacolums at once, instead as pointing out one field,
change that and move to the next column ... (Of cource I would prefer
to change all five talbes in one operation).

Regards

Peter
 
Hi,

The only way to do this in a pivot table is to write a macro. In that case
you should start out by recording whatever steps you would do manually and
the post your code so it can be modified.
 
Hi Peter

Debra Dalgleish has some example downloads at her site for this type of
thing.
This file here will deal with the first part of your request.
http://www.contextures.com/PivotMultiPagesDiffChange.zip

As far as the regular figures and percentage of column figures, why not
always show both?
Drag the same field to the data area again, and set its type to be % of
column.
Now drag the Data button and drop on Total, and you will have both value and
percentage shown side by side.
 
Hi Roger.

Thank you for your answers.

And thank you for telling me about Debra's page. I was not aware it
before.

Debra's file looks to be the right way. I have opend it, but I am not
sure if I have the right version from you. In her explaination box,
Debra tells me to "Select an item in one of this pivot table's page
fields". There is, however, no "item" to select at the sales-pivot
sheet.

The problem can, of course, be, that I am running a danish version. I
will look more into Debras code, and try to copy her idea into my own
file.

Your answer to my scond question was brilliant !!

Regards

Peter Grandjean
 
Hi agian Roger.

I found a "correct" version at Debra's page. But never the less: Thank
you for the assistance.

Peter
 
Back
Top