CODEWITHSUNDEEP
excelvba
ozo
ozo

Reputation: 943

How to copy the values of the first and last column of a pivot table?

I currently have code to copy the values from the pivot table and paste them onto a sheet:

the summary code is:

Sub Pivotcopy()

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("pivottable2")
    .
    .
    .
    pt.TableRange2.Copy    
    Sheets("newsheet").Range("A20").PasteSpecial xlPasteValues

However, this copies all the values. What I want is to copy only the first and last columns of the pivot table, i.e. the row labels column and the grand total column. After that I want to paste it in the "newsheet" sheet.

Upvotes: 0

Views: 867

Answers (1)

SJR
SJR

Reputation: 23081

Try this. You'd broken the back of it, just need to reference the two columns appropriately.

Sub Pivotcopy()

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("pivottable2")

With pt.TableRange2
    Union(.Columns(1), .Columns(.Columns.Count)).Copy
End With

With Sheets("newsheet").Range("A20")
   .PasteSpecial xlPasteValues
   .Resize(3).EntireRow.Delete shift:=xlUp
End With

End Sub

Upvotes: 1

Related Questions