I'm struggling with a code problem since yesterday. I dont get why I've got the following error message on line 3 : " Run-Time Error '5': Invalid Procedure Call or Argument"
Context: I'm trying to build a PivotTable on sheet called "test 2" from an data source sheet called "Extract".
Here's hereafter my VBA code:
Sub creation_TCD()
'
Sheets("Extract").Select
Columns("A:L").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Extract!A:L", Version:=xlPivotTableVersion16).CreatePivotTable _
TableDestination:="test 2! A1", TableName:="pt10", _
DefaultVersion:=xlPivotTableVersion16
Sheets("test 2").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("pt10").PivotFields( _
"UNIT")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("pt10").PivotFields( _
"LAST_OPERATOR")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("pt10").PivotFields( _
"DECISION_TYPE")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("pt10").AddDataField ActiveSheet. _
PivotTables("pt10").PivotFields("Nbre"), "Nombre de Nbre" _
, xlCount
ActiveSheet.PivotTables("pt10").AddDataField ActiveSheet. _
PivotTables("pt10").PivotFields("Time opérateur"), _
"Nombre de Time opérateur", xlCount
Range("C8").Select
Range("E12").Select
ActiveSheet.PivotTables("pt10").NullString = "0"
ActiveWindow.SmallScroll Down:=-6
Range("C5").Select
ActiveSheet.PivotTables("pt10").PivotSelect _
"'Nombre de Time opérateur'", xlDataAndLabel, True
With ActiveSheet.PivotTables("pt10").PivotFields( _
"Nombre de Time opérateur")
.Caption = "Somme de Time opérateur"
.Function = xlSum
End With
Range("B5").Select
ActiveSheet.PivotTables("pt10").PivotSelect _
"'Nombre de Nbre'", xlDataAndLabel, True
With ActiveSheet.PivotTables("pt10").PivotFields( _
"Nombre de Nbre")
.Caption = "Somme de Nbre"
.Function = xlSum
End With
With ActiveSheet.PivotTables("pt10").PivotFields( _
"DECISION_TYPE ")
.PivotItems("NEW ").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("pt10").PivotFields( _
"LAST_OPERATOR").CurrentPage = "(All)"
With ActiveSheet.PivotTables("pt10").PivotFields( _
"LAST_OPERATOR")
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("pt10").PivotFields( _
"LAST_OPERATOR").EnableMultiplePageItems = True
Range("B8").Select
End Sub
Could somebody help me out ? I feel like there 's a silly mistake, but can't solve it.