Pivot Tables

[Guest]

L.S.

Since I'm using pivot tables daily, I lose a lot of time 'lay-outing' them.
Usually I have only one field in the data area, and two or three in the
column area. Is it possible - instead of doing all manual - to autosort all
columns by the data field (descending) and for the layout of the data to have
no decimals (the number format) and a 1000 separator? Since I can't find an
option to change the default layout, should this be done with a script? If
so, how should this look like?

Thanks! Arjan

 

[Guest]

Hi Arjan,
It is a little difficult to understand how your data is laid out on the
worksheet with your description. Maybe you could give us an example of the
data you re dealing with? It lmost sounds like there is only 1 row of data
that has only 2 or 3 elements, so is hard to think that a pivot table is even
necessary?

 

[Debra Dalgleish]

You can change the number format of the data fields. For example:

Sub FormatDataFields()
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.DataFields
pf.NumberFormat = "#,##0"
Next pf
End Sub