How to export pivot table to a plain table?

  • Thread starter Thread starter RADO
  • Start date Start date
R

RADO

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
 
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
 
Tom - thanks, works very nicely! As always, you are very helpful - I much
appreciate your time and advice.

Best,
RADO
 
Bernie -

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

RADO
 
Back
Top