PDA

View Full Version : VBA Code to copy only the Pivot Data from a Pivot Table with out Grand Totals



bvsramesh
01-18-2012, 05:55 AM
Hi Team,

1) I need the VBA Code to copy only the Pivot table Data from a specific Pivot Table with out the headings and grand totals.

2) the above has to be done from two / three pivots and the two / three pivot table data should be merged in sheet one after another.

3) these pivots are in same range and same kind of headings but the data may be varable from one to another.

plz help me so that my work will be easier.

Regards,

SuryaRamesh

Paul_Hossler
01-18-2012, 06:35 AM
Not much detail to work with, but what I came up with


Option Explicit
Sub Combine()
Dim rDest As Range

Set rDest = Worksheets("Summary").Cells(1, 1)
Call CopyPivotTable(Worksheets("PT1").PivotTables(1), rDest)
Set rDest = rDest.Cells(1, 1).End(xlDown).Offset(1, 0)
Call CopyPivotTable(Worksheets("PT2").PivotTables(1), rDest)
Set rDest = rDest.Cells(1, 1).End(xlDown).Offset(1, 0)
Call CopyPivotTable(Worksheets("PT3").PivotTables(1), rDest)

End Sub


Sub CopyPivotTable(pt As PivotTable, r As Range)
Dim ptf As PivotField

With pt

For Each ptf In .PivotFields
ptf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
Next

.ColumnGrand = False
.RowGrand = False

Call .DataBodyRange.Copy(r)

End With
End Sub




No error checking at all


Paul

bvsramesh
01-18-2012, 08:03 AM
Hi Paul,

Thanks for the Quick Reply and very happy to c your reply but this is giving only half of my requirement only. please find the attached file for complete requirement. i hope you will solve it.

thanks

surya rameh