Copy PivotTable range from one worksheet to another worksheet. Code is copying active sheet data instead

Question

I have a PivotTable on worksheet (inventorySheet) and I want to copy the PivotTable data, which starts on cells P5 and Q5. I have a macro button I press on another worksheet (the sheet i want the data to be pasted on), but my code is copying data from the active worksheet instead of from inventorySheet.

I'm new to coding, but shouldn't my code Range("P5:Q5", Range("P5:Q5").End(xlDown)).Copy copy data from inventorySheet since it's within the With statement?

With inventorySheet
    .PivotTables("inventoryPivot").ClearAllFilters
    .PivotTables("inventoryPivot").PivotFields("Type"). _
        CurrentPage = "REGIONAL"
    Range("P5:Q5", Range("P5:Q5").End(xlDown)).Copy
End With

Thanks!

.Range("P5:Q5", .Range("P5:Q5").End(xlDown)).Copy Without the . it's not scoped to the With block — Tim Williams
Code stops on that line with run-time error with the . added in front of range, was wondering it was doing that — Automating_My_Life
What's the error? FYI instead of Range...End you can refer directly to the part(s) of the PT which you want to copy: that is the safer approach See peltiertech.com/referencing-pivot-table-ranges-in-vba — Tim Williams
Run-time error '1004': Method 'Range' of object '_Worksheet' failed Didn't know PT had specific references, that's good to know, thanks! How can I copy just the data portion? Not the headers but all the data below. Essentially the DataBodyRange but with the column to the left highlighted as well. — Automating_My_Life
Hi Tim, been working on this sparingly. I understand now why . is needed in front of both ranges. Believe it's so it calls from the inventorySheet. Otherwise without the ., even though it's within the With statement, it calls from the active sheet. Is this correct? — Automating_My_Life

Tim Williams Tim Williams · Accepted Answer · 2021-03-26T20:40:07

Something like this:

Dim rngCopy As range
With inventorySheet.PivotTables("inventoryPivot")
    .ClearAllFilters
    .PivotFields("Type").CurrentPage = "REGIONAL"
    'data body range plus one column to the left...
    With .DataBodyRange
        Set rngCopy = .Offset(0, -1).Resize( , .Columns.Count +1)
    End with
    rngCopy.Copy
End With

Copy PivotTable range from one worksheet to another worksheet. Code is copying active sheet data instead

1 Answers