pivot tables: changing 'sum of' to 'average of' & cycling through pages

[Tim]

hi... I'd love some pivot table help!

firstly: -

when i add fields to the pivot table (eg, drag in using the wizard), they
are always 'sum of' or 'count of', but i normaly want 'average of' is there
any way to adjust this (ie, change an option in preferences) or is there
some vba i can use to change all the fields in the data area to 'average
of'? whenever i've tried, it always seems to refer to all available fields
rather than those in the pivot-table.

secondly: -

i have collected data from several different production sites and i want to
make several summary pivot tables quickly/easily; can sometime tell me how i
can use vba loop through each site creating a new worksheet of sumary data
for each of the sites?

thanks for any pointers you can give me,

Tim

 

[Guest]

The default when you drag a new dimension onto the table is sum. You can
right click the dimension and select Field Setting and change it to average
(I assume you already knew this). To the best of my knowledge there is no way
to adjust what type of aggregation will be the default. The default is sum
and that is just the way it is...

As for your second question, if I understand you correctly, you want to
create a seperate worksheet for each member in a dimension. The answer
depends on whether you are using xl2000 orlater (pivot tables changed in
xl2002). If you are in 2000 then place the dimension you would like to
cascade in the selection criteria above the table. Right click and select
Show Pages. In xl2002 do the same thing but you select Show Pages from the
pivot table drop down on the pivot table menu.

 

[Tim]

Jim,

Point 1: yes, you assumed right! It was a 'shot in the dark' that there
would be an easy way of changing the default. I 'm just wondering if anyone
can advise how i can reference each of the chosen fields in the table to
change them to 'average of' - like i said originally, whenever i tried, i
could only actually refer to the available fields, not the chosen ones (i
hope that makes some kind of sense!)

Point 2: Thank you very much... I never knew that! That's exactly what i
wanted!

IDH*,

Tim

* = It Did Help

 

[Guest]

This code loops through all of the visible fields and attepts to change the
aggregation to average (xl2002)...

Sub test()
Dim pvt As PivotTable
Dim pvf As PivotField

Set pvt = ActiveSheet.PivotTables(1)
On Error Resume Next
For Each pvf In pvt.VisibleFields
pvf.Function = xlAverage
Next pvf
On Error GoTo 0
End Sub

 

[Tim]

Yep, that works perfectly and I see where i was going wrong

Thanks Jim,

Tim