VBA Copy Pivot Table to New Sheet

Question

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

Tim Williams · Accepted Answer · 2019-10-21T20:30:15.457

1

'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)

edited Oct 21 '19 at 20:30

answered Oct 21 '19 at 20:29

Tim Williams

154,628
8
97
125

Ha, of course, good catch. I've definitely seen this before (duplicate `CreatePivotTable(TableDestination:=PSheet.Cells(200, 200), TableName:="ObsoPivot")` should have given it away...) – BigBen Oct 21 '19 at 20:30
I am receiving error 1004: Unable to get the PivotTables property of the Worksheet class. This is when it tries to evaluate the 'Insert Data Fields part. – d3mo Oct 21 '19 at 20:47
What sheet is active when that errors? Note you already have a reference `PTable` to the new pivottable you just created - use that instead of `ActiveSheet.Pivottables(...)` – Tim Williams Oct 21 '19 at 20:48
Looks like ObsoCopy, now that you ask. Should I set activesheet before this step? – d3mo Oct 21 '19 at 20:49
No - there's no need to select sheets just to perform some action on them - in most cases it's the wrong thing to do since your code becomes reliant on knowing exactly which sheet is active at any time... https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Tim Williams Oct 21 '19 at 20:51
If I set PSheet = activesheet, does that also qualify as bad code? – d3mo Oct 21 '19 at 20:52
Whether that's "bad" depends on the context - sometimes you intend to operate on the active sheet, so it's noth that you *never* use `ActiveSheet` in your code: just that it's clear when you do that you meant it. – Tim Williams Oct 21 '19 at 21:23

VBA Copy Pivot Table to New Sheet

1 Answers1

Linked