Hi all,

I need some help. I have a pivot table, and I have to create it's plain copy
in a new workbook, but preserving the layout (i.e., it should be a plain
table looking exactly as the pivot table).

I tried to use the following code:

Sub Export_PivotTable()
Sheets("Table").PivotTables(1).PivotSelect "", xlDataAndLabel, True
Selection.Copy

Set New_Book = Workbooks.Add
New_Book.Sheets(1).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

However, it fails on line Selection.PasteSpecial Paste:=xlPasteValues
(gives Error 1004: PasteSpecial method..failed)

What's wrong with my code?

Or maybe anybody knows a more elegant way to export a pivot table to a plain
table without loosing the layout and formatting?

Thanks in advance -

RADO

RADO,

Your copied table is lost when you add your workbook. You need to add
the workbook prior to the copying to allow the pasting.

See the code example below.

HTH,
Bernie
MS Excel MVP

Sub Export_PivotTable()

Dim New_Book As Workbook
Dim Old_Book As Workbook

Set Old_Book = ActiveWorkbook
Set New_Book = Workbooks.Add

Old_Book.Activate
Sheets("Table").Activate
ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel
Selection.Copy

New_Book.Activate
Sheets(1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub


"RADO" wrote in message
...
Hi all,

I need some help. I have a pivot table, and I have to create it's
plain copy
in a new workbook, but preserving the layout (i.e., it should be a
plain
table looking exactly as the pivot table).

I tried to use the following code:

Sub Export_PivotTable()
Sheets("Table").PivotTables(1).PivotSelect "", xlDataAndLabel,
True
Selection.Copy

Set New_Book = Workbooks.Add
New_Book.Sheets(1).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

However, it fails on line Selection.PasteSpecial
Paste:=xlPasteValues
(gives Error 1004: PasteSpecial method..failed)

What's wrong with my code?

Or maybe anybody knows a more elegant way to export a pivot table to
a plain
table without loosing the layout and formatting?

Thanks in advance -

RADO

It is possible that adding a workbook is causing your data to be lost and
therefore you can't paste. Try doing the copy after the you add the
workbook.

Sub Export_PivotTable()
Dim rng as Range
set rng = Sheets("Table").PivotTables(1).TableRange2

Set New_Book = Workbooks.Add
New_Book.Sheets(1).Range("a1").Select
rng.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

--
Regards,
Tom Ogilvy


"RADO" wrote in message
...
Hi all,

I need some help. I have a pivot table, and I have to create it's plain
copy
in a new workbook, but preserving the layout (i.e., it should be a plain
table looking exactly as the pivot table).

I tried to use the following code:

Sub Export_PivotTable()
Sheets("Table").PivotTables(1).PivotSelect "", xlDataAndLabel, True
Selection.Copy

Set New_Book = Workbooks.Add
New_Book.Sheets(1).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

However, it fails on line Selection.PasteSpecial Paste:=xlPasteValues
(gives Error 1004: PasteSpecial method..failed)

What's wrong with my code?

Or maybe anybody knows a more elegant way to export a pivot table to a
plain
table without loosing the layout and formatting?

Thanks in advance -

RADO

Tom - thanks, works very nicely! As always, you are very helpful - I much
appreciate your time and advice.

Best,
RADO


"Tom Ogilvy" wrote in message
...
It is possible that adding a workbook is causing your data to be lost and
therefore you can't paste. Try doing the copy after the you add the
workbook.

Sub Export_PivotTable()
Dim rng as Range
set rng = Sheets("Table").PivotTables(1).TableRange2

Set New_Book = Workbooks.Add
New_Book.Sheets(1).Range("a1").Select
rng.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

--
Regards,
Tom Ogilvy


"RADO" wrote in message
...
Hi all,

I need some help. I have a pivot table, and I have to create it's plain
copy
in a new workbook, but preserving the layout (i.e., it should be a plain
table looking exactly as the pivot table).

I tried to use the following code:

Sub Export_PivotTable()
Sheets("Table").PivotTables(1).PivotSelect "", xlDataAndLabel, True
Selection.Copy

Set New_Book = Workbooks.Add
New_Book.Sheets(1).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

However, it fails on line Selection.PasteSpecial
Paste:=xlPasteValues
(gives Error 1004: PasteSpecial method..failed)

What's wrong with my code?

Or maybe anybody knows a more elegant way to export a pivot table to a
plain
table without loosing the layout and formatting?

Thanks in advance -

RADO

Bernie -

thanks for your help! You identified my problem correctly. my best regards -

RADO


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
RADO,

Your copied table is lost when you add your workbook. You need to add
the workbook prior to the copying to allow the pasting.

See the code example below.

HTH,
Bernie
MS Excel MVP

Sub Export_PivotTable()

Dim New_Book As Workbook
Dim Old_Book As Workbook

Set Old_Book = ActiveWorkbook
Set New_Book = Workbooks.Add

Old_Book.Activate
Sheets("Table").Activate
ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel
Selection.Copy

New_Book.Activate
Sheets(1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub


"RADO" wrote in message
...
Hi all,

I need some help. I have a pivot table, and I have to create it's
plain copy
in a new workbook, but preserving the layout (i.e., it should be a
plain
table looking exactly as the pivot table).

I tried to use the following code:

Sub Export_PivotTable()
Sheets("Table").PivotTables(1).PivotSelect "", xlDataAndLabel,
True
Selection.Copy

Set New_Book = Workbooks.Add
New_Book.Sheets(1).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

However, it fails on line Selection.PasteSpecial
Paste:=xlPasteValues
(gives Error 1004: PasteSpecial method..failed)

What's wrong with my code?

Or maybe anybody knows a more elegant way to export a pivot table to
a plain
table without loosing the layout and formatting?

Thanks in advance -

RADO