Converting pivot to flat file

[Guest]

Hi,

In a file i've created at least 20 tabs. Each tab with a pivot and a bunch
of formulas outside the pivot. I've created a macro that will save each tab
to a new file. I don't want to send the pivot but I want to keep the
formulas. My original way is to paste as values and then pasting the
required formulas back. This required a lot of work back and forth. And I
new to be informed of any new formulas that need to be added back.

Is there an easy way to paste the tab to a new file and convert the pivot to
flat file? Is there a way to select the pivot (no matter which cell range it
is), and then paste it back as values and format?

Thanks,
Carmen

 

[Dave Peterson]

I'm not sure what you mean by flat file--if you mean just plain old data, then
you could use a macro to do the work for you.

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim PT As PivotTable
Dim Wkbk As Workbook

Set Wkbk = ActiveWorkbook

For Each wks In Wkbk.Worksheets
wks.Copy 'to a new worksheet
With ActiveSheet
For Each PT In .PivotTables
With PT.TableRange2
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Next PT
'your code to save the new workbook
'.Parent.SaveAs Filename:=xxxx, FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With
Next wks

End Sub

This copy|pastespecial|values over the pivottable. Add your code to save that
new .xls file to this.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm