Repeating Values in Pivot Tables

Posted on November 12, 2013March 17, 2018 by Dick Kusleika

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

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

10 thoughts on “Repeating Values in Pivot Tables”

Jeff Weir says:

November 12, 2013 at 9:27 pm

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.
Vasco says:

November 13, 2013 at 4:45 am

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.

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

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

L. Quezada says:

November 22, 2013 at 10:50 am

Now I’ll call my girlfriend Ingrid “Dropzones”. True.
L. Quezada says:

November 22, 2013 at 11:51 am

I was replicating these steps in my machine but the option Repeat item labes is disabled. Do you know why or how can I enable it?

Regards.
Raghu says:

March 25, 2014 at 10:14 am

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
Dick Kusleika says:

March 31, 2014 at 11:05 am

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.
matt bach says:

June 10, 2016 at 1:45 pm

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
Hartmut says:

March 8, 2018 at 10:06 am

Quite similar functionality can be found in an addin that integrates into the Pivot table ribbon and is freely available via the website link. For the classic table layout the “Colorbands” button allows some nice coloring for the currently selected rowfields. Unfortunatley there is no documentation available for this addin. Some of the buttons have extra functionality by pressing the Shift or Ctrl key when clicking the ribbon control.
Ashley Dingle says:

February 15, 2019 at 3:52 am

Thank you , great post