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

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



Carmen wrote:

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