Private Sub CommandButton2_Click()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = Worksheets.Count To 1 Step -1
If Worksheets(i).Name = "TCD Resultat" Then
Application.DisplayAlerts = False
Worksheets("TCD Resultat").Delete
Application.DisplayAlerts = True
End If
Next i
If (ThisWorkbook.Worksheets("DetailMaperf").Cells(5, 3).Value <> "") Then
Worksheets("DetailMaperf").Activate
'Range("C4:M284").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"DetailMaperf!R4C3:R300C13").CreatePivotTable TableDestination:="", _
TableName:="TCD R", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("TCD R").PivotFields( _
"Type d'activité").Subtotals = Array(False, False, False, False, False, False, False, _
False, False, False, False, False)
ActiveSheet.PivotTables("TCD R").PivotFields( _
"Qualification de l'essai").Subtotals = Array(False, False, False, False, False, _
False, False, False, False, False, False, False)
ActiveSheet.PivotTables("TCD R").PivotFields( _
"Moyen d'essai").Subtotals = Array(False, False, False, False, False, False, False, _
False, False, False, False, False)
ActiveSheet.PivotTables("TCD R").AddFields RowFields:= _
Array("Type d'activité", "Qualification de l'essai"), ColumnFields:= _
Array("Données", "Moyen d'essai")
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Durée B1")
.Orientation = xlDataField
.Caption = "Somme de Durée B1"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Durée B2")
.Orientation = xlDataField
.Caption = "Somme de Durée B2"
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Durée IOD")
.Orientation = xlDataField
.Caption = "Somme de Durée IOD"
.Position = 3
.Function = xlSum
End With
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Durée EI")
.Orientation = xlDataField
.Caption = "Somme de Durée EI"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = True
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Name = "TCD Resultat"
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Type d'activité")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Moyen d'essai")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Qualification de l'essai")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("TCD R").PivotFields( _
"Qualification de l'essai")
.PivotItems("ETP").Visible = False
End With
End If
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Bookmarks