pivot table - dragging fields

[Raf Rollier]

I select a check box for a field in order to have it moved to the values area
of my pivot table;
however the field is numeric, sometimes it is moved by default to the row
label area
(sometimes it is moved by default to the values area for the same pivot
table : I can not find what the trigger is)

how can I avoid this or how can I drag "in bulk" all fields from the row
label to the values area ?
thanks !

 

[Raf Rollier]

Good morning,

version : Excel 2007 SP 1 MSO

I want only column fields (no problem) and fields in the value area.
when dragging a second field to the values area, automatically a "Values
field" is added (to the column label area, I drag it then to the row field
area)
so far so good

then I want, by selecting the check box of the fields, add other fields to
the value area. sometimes they are adde to the row area (I don't want that,
I want them in the value area)

do you know any vba code to move them "in bulk" or better (because moving
them in bulk will force me to move every field up or down ...) find a way
that by clicking the check box of the fields, they are moved to the area I
want (defined in advance).

THANKS FOR YOUR HELP !!!

 

[Raf Rollier]

THANK YOU VERY MUCH !!!

Hi

Excel will default to the Row area, if ALL the data in that field is
Numeric.
If there are any text values, or any blank values, then it will default to
adding the field to the Row area.
If you right click on the field label, you have the options show as to which
area you want the field allocated.

The following code will add all fields to the Data area and set their
property to Sum
You will then need to pull any fields you require to the Column or Row area.

Sub AddAllFieldsToDataArea()
Dim pt As PivotTable, pf As PivotField
Dim ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.PivotFields
With pf
.Orientation = xlDataField
.Function = xlSum
End With
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight the macro name
Run