How to get your Preferred Pivot

Use a VBA Macro to Automate this

By Wyn Hopkins

If you use Pivot Tables regularly then you’ve probably been frustrated by the default layout you get when you initially create one

For example to get from the BEFORE view to the AFTER view below takes about 10 clicks of the mouse.


Alternative approach is to use VBA and assign this macro to a Quick Access Toolbar.

You can either store the code in your Personal Macro Workbook, or if you want others to use it then store it in the Workbook itself.

Free Download

Sub PreferredPivot()
'User must have a cell within a pivot table selected before proceeding
    Dim myPT As PivotTable
    Dim ptField As PivotField
'Check user is clicked inside a Pivot Table
    On Error Resume Next
    Set myPT = ActiveCell.PivotTable
    On Error GoTo 0
    If myPT Is Nothing Then
        MsgBox "Please click inside a Pivot Table", vbInformation, "No Pivot Selected"
        Exit Sub
    End If
    Set myPT = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
'Set as Tabular
    myPT.RowAxisLayout xlTabularRow
'remove subtotals
    For Each ptField In myPT.RowFields
        On Error Resume Next
        ptField.Subtotals(1) = False
    Next ptField
'additional formatting preferences
        myPT.RepeatAllLabels xlRepeatLabels
        myPT.ShowTableStyleRowHeaders = False
        myPT.ShowDrillIndicators = False
        myPT.HasAutoFormat = False
        Set myPT = Nothing
End Sub