Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet

Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet
Here is how you can copy a specific pivot field and display its data on another worksheet. In the following pictures, only the columns of a pivot table’s Sales field are copied to Sheet2 so you can work with that Sales data independent of the original pivot table. The CopyPivotField macro’s code purposely includes the State headers and the Grand Total rows in this example with the Offset and Resize methods, which you can modify if you don’t want those rows in your copied data.
TET3a
TET3b

Sub CopyPivotField()
With ActiveSheet.PivotTables(1)
With .PivotFields("Sum of Sales").DataRange
.Offset(-1).Resize(.Rows.Count + 2).Copy Sheet2.Range("A1")
End With
End With
End Sub

This one-liner (the line continuation underscore character is for readability on the web page) in a macro or in the Immediate window copies a PivotField’s data without a header or totals row:

ActiveSheet.PivotTables(1).PivotFields("Sum of Sales") _
.DataRange.Copy Sheet2.Range("A1")

TET3c

Share Button
Posted in Tom's Tutorials for Excel
Tags:
One comment on “Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet

Leave a Reply

Your email address will not be published.

*