Copy dynamic data range (from PivotTable) from one worksheet and paste to another sheet

I have a macro button on worksheet2, and my data (PivotTable) on worksheet1. I want to:

1. Push macro-button on worksheet2
2. Macro clears PivotTable and selects a certain field on worksheet1
3. Copy the PivotTable on worksheet1
4. Paste to a specific cell on worksheet2

My macro does everything but it does not copy the data from worksheet1, it copy's the data from worksheet2 for some reason. I have the code within a With statement, not sure why it's copying data from the activesheet (worksheet2).

Sub muniButton()    Application.ScreenUpdating = False    ' Refresh PivotTable and copy source data            With worksheet1          .PivotTables("inventoryPivot").ClearAllFilters          .PivotTables("inventoryPivot").PivotFields("Type"). _              CurrentPage = "REGIONAL"          Range("P5:Q5", Range("P5:Q5").End(xlDown)).Copy      End With        ' Pasting source data        worksheet2.Range("outputCorner").Offset(1, 0).PasteSpecial      Application.CutCopyMode = False    End Sub  

I'm not including . in front Range("P5:Q5", Range("P5:Q5").End(xlDown)).Copy because it gives an error. Shouldn't this copy data from worksheet1 since it's within the With statement? For some reason it copys the data from worksheet2 since it's the activesheet.

Thanks.

https://stackoverflow.com/questions/66837761/copy-dynamic-data-range-from-pivottable-from-one-worksheet-and-paste-to-anothe March 28, 2021 at 09:56AM