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 !

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:
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

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

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

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.

JMay wrote:
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






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

On Sun, 06 Jun 2004 10:48:26 -0400, Debra Dalgleish
wrote:

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
'==========================
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.

Thanks for the clarification Debra;
It's all about learning and discovery...
JMay

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

JMay wrote:
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






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