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.

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