Copy "Grand Total" from Pivot table, using VBA

[axf80]

I'm trying to use VBA to copy the Total column from a dynamic pivot table but keep getting an error.

Here is my code:

MyPivotTable.PivotFields("Name").PivotItems("Grand Total").DataRange.Copy
Sheets("Sheet 1").Range("A1").PasteSpecial xlPasteValues

This approach works fine for normal columns in the pivot table, but doesn't seem to work for the Sum column labelled "Grand Total". Do I need to refer to this column differently?

Many thanks

 

[Comfy]

I don't think there is a Grand Total pivot Item.

You can try this:

Option Explicit
Sub RowGrandTotal()
Dim pvt As PivotTable
Dim rng As Range


Set pvt = ActiveSheet.PivotTables("PivotTable3")
Set rng = Range(pvt.DataBodyRange.Cells(1).Offset(pvt.DataBodyRange.Rows.Count - 1, 0), pvt.DataBodyRange.Cells(1).Offset(pvt.DataBodyRange.Rows.Count - 1, pvt.DataBodyRange.Columns.Count - 1))
rng.Select


End Sub
Sub ColumnGrandTotal()
Dim pvt As PivotTable
Dim rng As Range


Set pvt = ActiveSheet.PivotTables("PivotTable3")
Set rng = Range(pvt.DataBodyRange.Cells(1).Offset(0, pvt.DataBodyRange.Columns.Count - 1), pvt.DataBodyRange.Cells(1).Offset(pvt.DataBodyRange.Rows.Count - 1, pvt.DataBodyRange.Columns.Count - 1))
rng.Select


End Sub

 

[axf80]

Thanks for your response Comfy - I'm not quite sure what it's doing, but it works great! Just what I was after!