I often use pivot tables to summarise one data source so that I can use that pivot table output as the basis for further analysis.

By working on summarised data I can reduce the overall file size by losing unnecessary detail and it is also a useful way to control the format of the data as well.

I find myself spending quite a bit of time adjusting the format of the pivot table so that it looks like a flat file data source that I can then copy and paste values as a new data source.

The stages to format a modern styled pivot table to a flat file style are:

1. Convert to classic style
2. Remove all subtotals
3. Repeat item labels

If you do a similar thing you might appreciate this macro that automates the process

Sub PivotFieldsRepeat()

'Created by Dr Moxie from code assembled from the web
'Great tips from Contextures:
'http://blog.contextures.com/archives/2015/02/12/hide-pivot-table-subtotals/

'The macros sets the pivot into classic mode, repeats all labels and removes subtotals
'Setting you up to copy and paste the pivot table as a flat file data source if required
        
    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
            .InGridDropZones = True
            .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)
                
            'My preferred option is to set the automatic so that only one subtotal option is selected
            'Then turn automatic subtotals off
                pf.Subtotals(1) = True
                pf.Subtotals(1) = False
                
            'This section then repeats the item labels
                pf.RepeatLabels = True
            End If
        Next pf
    End If
    
End Sub

If you want to reverse this macro and put your classic styled pivot table back to the new fangled Excel 2007 style, you’ll need to use this macro:

Sub ReturnToModernPivotStyle()
 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 the modern style

    With pt
        .InGridDropZones = False
        .RowAxisLayout xlCompactRow
        .TableStyle2 = "PivotStyleLight17"
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
    End With

    For Each pf In pt.PivotFields
            If pf.Orientation = xlRowField Then
            
         
            'Turn automatic subtotals back on
                pf.Subtotals(1) = True
               
                
            'This section then removes the repeating item labels
                pf.RepeatLabels = False
            End If
        Next pf
    End If

End Sub

Here’s the example workbook with a working macro to both flatten the modern style pivot table (repeating item labels and removing subtotals) and a macro to return the classic style pivot table to the modern design. Pivot Macros workbook.xlsm