Is it possible to automate pivot table formatting plz?

[Bled]

Hello,

I always always use the same pivot table formating, checking 3 things before using it:

- Repeat item labels

- Show in Tabular Format

- Hide Subtotals

I know 3 clicks doesn't sound time consuming, but when you are working with pivot tables all day it can be.

I tried recording macro , but the result macro take more time than when done manually.

Can this formatting be by default? or is there VBa macro to help with it?

Any help appreciated, thanks

 

[VBE313]

Paste the macro that you recorded

 

[Bled]

Paste the macro that you recorded

I just found this macro, does a good job so far, maybe it can help someone:

Sub CleanPivot()

Dim pt As PivotTable
Dim pf As PivotField

On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo 0

If Not pt Is Nothing Then

'sets the pivot table into classic format
With pt

.RowAxisLayout xlTabularRow
End With

For Each pf In pt.PivotFields
If pf.Orientation = xlRowField Then

'This option sets all subtotal options (Sum, Count, Avg etc) to false
'pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
pf.Subtotals(1) = True
pf.Subtotals(1) = False
pf.RepeatLabels = True
End If
Next pf
End If
End Sub