Repeating Values in Pivot Tables

Often I’m using a PivotTable to aggregate some data to use elsewhere. I’ll take a PivotTable that looks like this

and make it look like a grid so that I can copy and paste it somewhere else. To do that, I first go to the PivotTable Options – Display tab and change it to Classic PivotTable layout.

Then I’ll go to each PivotItem that’s a row and remove the subtotal

and check the Repeat item labels checkbox.

And I get a PivotTable that’s ready for copying and pasting.

After about 50 times of doing that, I got sick of it. Now I just run this code.

Sub PivotGrid()

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
With pt
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With

For Each pf In pt.PivotFields
If pf.Orientation = xlRowField Then
pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
pf.RepeatLabels = True
End If
Next pf
End If

End Sub

8 thoughts on “Repeating Values in Pivot Tables

  1. Note that you don’t have to change it to Classic View to do this. Just go Design/Report Layout/Show in Tabular Format. At which stage the Report Layout menu ‘helpfully’ vanishes. So then you need to click on it again, and select Repeat All Item Labels.

    But I too use code.

  2. It is incredible how microsoft makes our life harder.
    Pivot tables are now so much more difficult to use. It’s a shame.
    Why is there not a default format for pivots just to work as it always did? Not all pivots are to do fancy presentations.
    Thanks for sharing.

  3. How nice to see! In my personal custom tab in Excel 2010 a button runs this macro:

    Public Function SetPivots()
        Dim pt As Excel.PivotTable
        Dim pf As Excel.PivotField
        For Each pt In ActiveSheet.PivotTables
            With pt
                .ShowDrillIndicators = False
                .RowAxisLayout xlTabularRow
                .ShowTableStyleRowHeaders = False
                .HasAutoFormat = False
                .PivotCache.MissingItemsLimit = xlMissingItemsNone
                For Each pf In .RowFields
                    pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
                Next pf
                .TableRange1.EntireColumn.AutoFit
            End With
        Next pt
    End Function
    

    Find the differences. Default I don’t repeat the row labels, but you could add the line pt.RepeatAllLabels xlRepeatLabels. After installing I changed the Sub into a Function so it won’t show in Alt-F8.
    Hope this helps.
    – Frans

  4. Hi

    I am trying to use this code in Excel 2007 but give error “Runtime error 438”. But it works fine in excel 2010. Please let me know if any workaround for 2007?

    Sub PivotGrid()

    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
    With pt
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    End With

    For Each pf In pt.PivotFields
    If pf.Orientation = xlRowField Then
    pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    pf.RepeatLabels = True
    End If
    Next pf
    End If

    End Sub

  5. Raghu: I don’t have 2007 to test on anymore. If you record a macro in 2007 that does all this stuff, you should be able to see what syntax changed between the two versions. It has to be one of: InGridDropZones, .RowAxisLayout, .Subtotals, or .Orientation. If you can’t figure it out, paste the recorded macro in a comment here (don’t forget the <code> tags) and someone will see the difference.

  6. I can not find a way to repeat the row labels (for the values, i.e. revenue, sales volume etc.) in my PT
    I’ve already tried all the simple fixes for this issue.
    FYI – my data is OLAP

Leave a Reply

Your email address will not be published. Required fields are marked *