CODEWITHSUNDEEP
excelvba
d3mo
d3mo

Reputation: 9

VBA Copy Pivot Table to New Sheet

I am trying to copy a pivot table to an existing sheet.
I have successfully copied a previous pivot table to this sheet, which is causing me issues when copying a second one.

Current error message is:

Run-time error '1004': Application-defined or object-defined error.

I want to paste the pivot table in cell D64, as the cells below and to the right of this cell are all clear. My existing pivot table is in cell A64:B36.

Can anyone help figure out what I am doing wrong? 

Sub ObsoPivotCopy
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim Prange As Range
Dim lastRow As Long
Dim lastCol As Long

Set PSheet = Worksheets("1188 MJ Summary")
Set DSheet = Worksheets("ObsoCopy")

'Define Data Range
lastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set Prange = DSheet.Cells(1, 1).Resize(lastRow, lastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Prange). _
CreatePivotTable(TableDestination:=PSheet.Cells(200, 200), _
TableName:="ObsoPivot")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(200, 200), TableName:="ObsoPivot")

'Insert Data Fields
ActiveSheet.PivotTables("ObsoPivot").AddDataField ActiveSheet.PivotTables( _
        "ObsoPivot").PivotFields("MARGIN €"), "Sum of MARGIN €", xlSum
    ActiveWindow.SmallScroll Down:=21
    ActiveSheet.PivotTables("ObsoPivot").PivotFields("Sum of MARGIN €"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("ObsoPivot").AddDataField ActiveSheet.PivotTables( _
        "ObsoPivot").PivotFields("NET MARGIN €"), "Sum of NET MARGIN €", xlSum
End Sub

Upvotes: 0

Views: 232

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Prange). _
     CreatePivotTable(TableDestination:=PSheet.Cells(200, 200), TableName:="ObsoPivot")

you're creating a pivotcache and then directly calling CreatePivotTable on that cache: that will return a PivotTable, not a PivotCache...

So you only need the first part:

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Prange)

Upvotes: 1

Related Questions