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



"Debra Dalgleish" wrote in message
...
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
'==========================

Nigel Bushell wrote:
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 !


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html