Copy used range from one worksheet to another (and lose the pivot)

[Barb Reinhardt]

I have a pivot table on a worksheet I'm calling myWS. I'd like to copy the
used range of this worksheet to another worksheet I'm calling ResWS, but lose
the pivot. How would I do that programmatically?

Thanks,
Barb Reinhardt

 

[JLGWhiz]

Hi Barb, I don't use Pivots as a rule but it seems to me that when you set it
up, it creates a lot of precedents and dependents that are all tied to the
sheet. So I would imagine that might be why they are all destroyed when you
copy to a new sheet. Just makes a clean copy without the link complications.
Of course, this is a guess.

 

[Barb Reinhardt]

I'm not sure you understood my question. I want to copy what's in the pivot
to a new worksheet and lose all of the precedents/dependents. How do I do
that programmatically?

 

[ryguy7272]

This worked for me:

Sub Macro2()

Range("A3").Select 'Or select a cell within your Pivot Table
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
Selection.Copy
Application.Goto Reference:="xxx-your-named-rangexxx"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

When you right-click on the Table, there is a Select > Entire table
option...I think that's the key here...

HTH,
Ryan---