Modify Pivot Excel 2003

[Forestq]

Team,

pelase tell how can I add to my code "pivot cache" and how to create PivotTable in sheet call "PivotSheet" (which I`m creating by VBA).

this is my code:

Sub CreatePivot()
   
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("PivotSheet").Delete
    On Error GoTo 0
    
    Worksheets.Add
    ActiveSheet.Name = "PivotSheet"
    ActiveWindow.DisplayGridlines = False
    
    
      Sheets("Sheet1").PivotTableWizard _
      SourceType:=xlDatabase, _
      SourceData:="A1:G40", _
     TableName:="Sales&Trans"
        
    With ActiveSheet.PivotTables("Sales&Trans")
        .PivotFields("Year").Orientation = xlPageField
        .PivotFields("Year").Position = 1
        .PivotFields("Store City").Orientation = xlRowField
        .PivotFields("Store City").Position = 1
        
        .PivotFields("Store Type").Orientation = xlRowField
        .PivotFields("Store Type").Position = 2
        
        .PivotFields("Period").Orientation = xlColumnField
        
        
        With .PivotFields("Transactions")
            .Orientation = xlDataField
            .Position = 1
        End With
        
        With .PivotFields("Sales")
            .Orientation = xlDataField
            .Position = 2
        End With
        
    End With
                 
 End Sub

thanks for your support!
regards,
PvK

 

[Andrew Poulsom]

Try:

Sub CreatePivot()
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("PivotSheet").Delete
    On Error GoTo 0
    Worksheets.Add
    ActiveSheet.Name = "PivotSheet"
    ActiveWindow.DisplayGridlines = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R40C7").CreatePivotTable TableDestination:= _
        "PivotSheet!R1C1", TableName:="Sales&Trans", DefaultVersion:= _
        xlPivotTableVersion10
    With ActiveSheet.PivotTables("Sales&Trans")
        .PivotFields("Year").Orientation = xlPageField
        .PivotFields("Year").Position = 1
        .PivotFields("Store City").Orientation = xlRowField
        .PivotFields("Store City").Position = 1
        .PivotFields("Store Type").Orientation = xlRowField
        .PivotFields("Store Type").Position = 2
        .PivotFields("Period").Orientation = xlColumnField
        With .PivotFields("Transactions")
            .Orientation = xlDataField
            .Position = 1
        End With
        With .PivotFields("Sales")
            .Orientation = xlDataField
            .Position = 2
        End With
    End With
End Sub

 

[Forestq]

works perfect!
thanks a lot for help