copying output from pivit tables

N

Nigel Bushell

I need to copy the output from a pivot table from one sheet to another
in order to re-format the output for report purposes
If i manually select the block,copy and paste, then I have the values
and formatting pasted fine into the other sheet as required.

How would I go about this using VBA?
The table output is dynamic in the size of the final range so I have
tried using the TableRange1 property which doe indeed give me the
correct range address. However whan I try to paste using the output
from this I always get a complete pivottable copied not just the
formatted data which is what I would like !

I am an experienced programmer but an Excel idiot

Any advice would be very welcome !
 
D

Debra Dalgleish

The following code copies the pivot table and formatting to sheet1. To
include the page fields, use TableRange2:

'=========================
Sub CopyPivotTable()
Sheets("Pivot").PivotTables(1).TableRange2.Copy
With Sheets("Sheet1")
.Activate
.Range("A1").PasteSpecial Paste:=xlPasteValues
.Range("A1").PasteSpecial Paste:=xlPasteFormats
End With
End Sub
'==========================
 
J

JMay

Debra:
To test this example through, I did a PT without a Page.
Accordingly, I omitted the "TableRange2" from the below
so I have:
Sub CopyPivotTable()
Sheets("Pivot").PivotTables(1).Copy <<< I get Error 438 Obj doesn't
support this prop or method >> What's wrong????
With Sheets("Sheet1")
.Activate
.Range("A1").PasteSpecial Paste:=xlPasteValues
.Range("A1").PasteSpecial Paste:=xlPasteFormats
End With
End Sub
 
D

Debra Dalgleish

The original poster's code used TableRange1, and I substituted TableRange2.
 
J

JMay

Thanks Debra;
I didn't realize that TableRange1 and TableRange2 are both VBA Constants.
I included TableRange1 in my Line one as follows:

Sheets("Pivot").PivotTables(1).TableRange1.Copy

And every thing works fine.

In Passing I
saw in VBA help Regarding TableRange2 << includes page fields
Does this have to do with the usage of the Page component of the PT,
or something else?

Playing with a half-a-deck here, sorry!!
 
D

Debra Dalgleish

If you have fields in the page area of the pivot table, they won't be
copied if you use the TableRange1 property.

The page fields will be copied if you use TableRange2.
 
N

Nigel Bushell

The following code copies the pivot table and formatting to sheet1. To
include the page fields, use TableRange2:

'=========================
Sub CopyPivotTable()
Sheets("Pivot").PivotTables(1).TableRange2.Copy
With Sheets("Sheet1")
.Activate
.Range("A1").PasteSpecial Paste:=xlPasteValues
.Range("A1").PasteSpecial Paste:=xlPasteFormats
End With
End Sub
'==========================
Click to expand...
Debra,
Works like a charm!
I didn't realise that you could paste properties in several goes.
I had several attempts at this,each with sysntax only slighlty
different from yours, but still kept getting the full-blown table.

Many thanks indeed.
 
Top