MattN

Pivot Tables.

Does anyone know how to select the whole pivot table in VBA.

I want a macro that creates a pivot table then copies it, but I'm having real problems selecting the whole thing.

Anyone?????

fast bloke

Can you not se the Macro recorder to get the code?

MattN

nope, doesn't like it,

this is what I have so far -

Dim mypivot As PivotTable
Dim myfield As PivotField
Dim myitem As PivotItem
Dim myrange As Range

Set mypivot = ActiveSheet.PivotTableWizard
Set myfield = mypivot.PivotFields("T1-Gross-Qty")
myfield.Orientation = xlDataField
Set myfield = mypivot.PivotFields("ISBN")
myfield.Orientation = xlRowField
mypivot.AddFields "ISBN", "Rep-Area"


****now in here I want to select the whole table*****

Martingb

Use code to select a cell within the pivot table and then use the current region command

e.g.
range("a1").select
Selection.CurrentRegion.Select

Martin.

fast bloke

Range("range of pivot table").Select
Selection.Copy
Range("top left corner of where you want to put it").Select
ActiveSheet.Paste



Just tried this and it works for me. Also updates new table on a refresh, so it is actually copying the table and not simply the contents

fast bloke

p.s. for the range of pivot table, use A1:B3 format, specifying top left and bottom right corners

MattN

cheers Martin.

FB, the problem with selecting the range is the pivot tables in this macro are all different sizes and when new ones are created are different sizes again, meaning I couldn't just selct a range.

Cheers guys

fast bloke

I thought it was too easy