is there a way to set the defaults of a pivot table?

kgittinger
kgittinger used Ask the Experts™
on
is there a way to set the defaults of a pivot table ?
I would like the classic (drag and drop) and numeric (comma seperator with 2 decimal places)

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011

Commented:
No there isn't I'm afraid. You would have to use code to reformat afterwards (that's what I do to set data fields to Sum and format them) Is code an option?

Author

Commented:
May I see an example of the code you are referring to?
Most Valuable Expert 2011
Top Expert 2011
Commented:
Sure:


Public Sub FormatPivot2007()
    Dim ptf As PivotField
    With Selection.PivotTable
        ' set to classic layout
        .InGridDropZones = True
        ' then format the data fields
        .ManualUpdate = True
        For Each ptf In .DataFields
            With ptf
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
        Next ptf
        .ManualUpdate = False
    End With
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial