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!!
"Debra Dalgleish" wrote in message
...
The original poster's code used TableRange1, and I substituted
TableRange2.
JMay wrote:
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
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html