Sub CreatePivotTable()
Dim myWorksheet As Worksheet
Dim pvc As PivotCache
Dim pvt As PivotTable
Set myWorksheet = Worksheets.add
Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheet1.ListObjects("Table1").range)
Set pvt = pvc.CreatePivotTable(TableDestination:=myWorksheet.range("A3"), _
DefaultVersion:=xlPivotTableVersion12)
With pvt
With .PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
.AddDataField .PivotFields("NumberSold"), "Sum of NumberSold", xlSum
End With
End Sub