Copy multiple columns from pivot tabe (excluding last row) to new worksheet

[wjeil]

Hello,

I have a pivot table with 4 columns which I want to select the whole range and copy to another worksheet in (A2:D2 till the end of the row of the pivot table).

I have this code.
Dim rng As Range
Set rng = ActiveSheet.PivotTables("Draaitabel1").DataBodyRange
Set rng = rng.Resize(RowSize:=rng.Rows.Count - 1)
rng.Select

Which only selects the last column of the pivot table but excludes the last row. How can I copy all of the columns and copy them to a certain destination?

 

[wjeil]

Because the pivot table is "foldable" my code gives the following result if i want to select the first 4 columns.

With ActiveSheet.PivotTables("Draaitabel1").DataBodyRange.Columns("A:D")
.Resize(RowSize:=.Rows.Count - 1).Select
End With

If i do it manually and copy only the values it works, so why does this command not selects column A:D like i say in the code?

 

[wjeil]

I figured out that the data in the first 3 columns (and the header) are labels (or rowlabels?) and that the ColliAmount in D are the values (or data)? Maybe it is better to select the whole pivot table, copy it as value and the delete the first and last row (so the header and the total at the end of the range). Am I correct?