I use Xl2003 and am trying to create a new file with 5 worksheets. Each
worksheet will hold a pivottable with sales data.

The pivottable gets its data from the sourcefile with 5 worksheets, each one
holding data of a different product family ("tennis", "bikes", "summer
clothing", "winter clothing" and "fitness"). Each sheets holds sales data in
between 4.000 and 60.000 rows (minimum and maximum), that is being imported
from a database (MS Navision).

Or maybe I should simply create pivot tables in same workbook and then
afterwards move the sheets to another workbook?

The source data file is 60MB. My boss wants only to see the pivottables,
which together with the sourcefiles would be 120MB!!! Putting the pivottables
in a seperate file I hope to reduce size.

All help/advice appreciated!

My code in the source file is:

**** START CODE **************************************
Option Explicit
Dim strWSDatos As String
Dim strRangoDatos As String
Dim PTCache As PivotCache
Dim pt As PivotTable
Dim intDistancia As Integer
Dim SourceWB As Workbook
Dim SourceWS As Worksheet
Dim DestWB As Workbook
Dim DestWS As Worksheet
Sub Crear_TablasDinamicas_Ventas()
'

Dim xlCalc As XlCalculation
Dim i As Integer

'names PivotTables
Dim strTD1 As String

'name file to export PT to
Dim strDestWB As String
Dim strDestWS As String

Dim intNumberSheets As Integer
Dim strPath As String


strTD1 = "Pivot_TENIS"

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
ActiveWindow.DisplayGridlines = False
ActiveSheet.DisplayAutomaticPageBreaks = False

'define source workbook which holds data queries
Set SourceWB = ActiveWorkbook

intNumberSheets = SourceWB.Sheets.Count

'create new file
Workbooks.Add
Windows.Arrange ArrangeStyle:=xlHorizontal
'how to create 5 sheets?

'delete all sheets in destination wb except 1
For i = ActiveWorkbook.Sheets.Count To 2 Step -1
Sheets(i).Delete
Next 'i

'give name to new file which will contain pivot tables with sales
strDestWB = "Sales Statistics- " & Format(Now, "yyyymmdd")
'determine path where new file with sales data will be saved
strPath = "C:\Documents and Settings\usuario\Escritorio" 'Yes I work in
Spain!

'define destination workbook where pivot tables will be created
Set DestWB = ActiveWorkbook

'save destination workbook
DestWB.SaveAs Filename:=strPath & Application.PathSeparator & strDestWB
& ".xls"

'activate source WB
SourceWB.Activate

On Error Resume Next

'activate source file
SourceWB.Sheets(i).Activate
'determine name of active sheet to be copied to destination WS
strWSDatos = ActiveSheet.Name
strDestWS = strWSDatos
Set SourceWS = SourceWB.Sheets(strDestWS)

'Give name to sheet in destination WB
DestWB.Sheets(1).Name = strDestWS

Set DestWS = Workbooks(DestWB.Name).Sheets(strDestWS)

strRangoDatos = "bbdd"

'Define data range
strRangoDatos = "bbdd_" & strWSDatos

SourceWB.Names.Add Name:=strRangoDatos, RefersToR1C1:= _
"=OFFSET('" & strWSDatos & "'!R1C1,0,0,COUNTA('" & strWSDatos &
"'!C24),COUNTA('" & strWSDatos & "'!R1))"

SourceWB.SourceWS.Activate

'Create Pivot Cache
Set PTCache = SourceWB.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("'" & strDestWS & "'!" & strRangoDatos).Address)

'Create the Pivot Table from the Cache
Call Create_PivotTable_Tenis


With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
End With

DestWB.Close SaveChanges:=msoTrue

End Sub

Sub Create_PivotTable_Tenis()

Dim strTD1 As String
strTD1 = "TD_TENIS"

MsgBox DestWB.Name & "-" & DestWS.Name
DestWB.DestWS.Activate

'Create the Pivot Table from the Cache
Set pt = PTCache.CreatePivotTable( _
TableDestination:=DestWB.DestSh.Range("A1"), _
TableName:=strTD1)

** THIS DOESN´T WORK: WHY?
*** CAN I NOT USE CACHE FROM 1 WORKBOOK TO PASTE IN ANOTHER WB?

On Error Resume Next
With ActiveSheet.PivotTables(strTD1)
With .PivotFields("Importe línea")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
With .PivotFields("doc.")
.Orientation = xlColumnField
.Position = 1
.PivotItems("Pedidos").Visible = False
.PivotItems("PedInicial").Visible = False
.PivotItems("(en blanco)").Visible = False
End With
With .PivotFields("temp")
.Orientation = xlRowField
.Position = 1
.PivotItems("03-04").Visible = False
.PivotItems("04-05").Visible = False
.PivotItems("05-06").Visible = True
.PivotItems("(en blanco)").Visible = False
End With

End With
End Sub

********* END CODE *************