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.
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
.InGridDropZones = True
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