Converting pivot to flat file
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 |
Converting pivot to flat file
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 |
All times are GMT +1. The time now is 02:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com