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