Export pivot table and values to new workbook.

Hello,

I have a series of pivot tables in a workbook that all like to a very large data sheet that is formula driven in this file. There are other users that require use of the tables and part of the data, but not the whole file. I am trying to find a way that I can export the pivot tables and the data source (converting it to values instead of the formulas) quickly into a new workbook after each refresh.

I am currently using the vba code to transfer the data table:

Set newwb =workbooks.add
For i = 15 to 16
ThisWorkbook.Activate
Sheets(i).Copy after:=newwb.Sheets(1)
Range("A1", Cells.SpecialCells(xlCellTypeLastCell)).Value = Range("A1", Cells.SpecialCells(xlCellTypeLastCell)).Value
next i

However, it does not transfer as values, all the formulas stay in place, and it is driving the size of my file quite high.

Any assitance that could be provided would be very helpful.

If you're saying you want to copy the Pivot and source data to a new file then one method might be along the lines of:

Sub Example()
    Dim boolGTVis As Boolean
    With Sheets.Add
        Sheets("Sheet1").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
        With ActiveSheet.PivotTables(1)
            boolGTVis = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = boolGTVis
            .Parent.Activate
        End With
    End With
End Sub

obviously modify per own requirements but in essence the above:

- copies the Pivot to a new sheet
- moves the new sheet to a new file
- drills through on to the Grand Total of that Pivot (to generate values only data set)
- modifies the cache for the Pivot such that it uses this new static data set (from that originally stored in the main file)

It might be nice to share your workaround for the benefit of others who may come across your thread whilst trying to resolve their own issues in the future.