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.