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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |