Copy the data in a Pivot Tabe to another tab

[gheyman]

I want to have a button that copies the data from a pivottable to a new tab as data (not another Pivot tale). can you automate this? Can you use the Pivot table name in the code instead of having to select it manually?

PivotTable1

 

[Misca]

Jon Peltier wrote an excellent article on how to reference Pivot Table Ranges in VBA:

https://peltiertech.com/referencing-pivot-table-ranges-in-vba/

Depending on your PT structure you may have to add an offset or resize the ranges but everything you need to know is in the article.

To make sure you're referencing the right PT you can use pivot table names in your code. Jon uses the index number in his example but the same can be done with names:

Sub CopyPTRange()

Dim PT As PivotTable
Dim Rng As Range
Dim WS As Worksheet


Set PT = Sheets("Sheet1").PivotTables("PivotTable1")


Set Rng = PT.TableRange1


Set WS = ThisWorkbook.Sheets.Add


With WS
    .Cells(5, 2).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
End With


End Sub