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.
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")
test