Option Explicit
Dim wsData As Worksheet, wsPT As Worksheet
Dim rngData As Range
Dim ptCache As PivotCache
Dim pt As PivotTable
[cpp] Sub TCDautomatique3_Bouton2_Cliquer()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wsData = Worksheets("Données3" )
Set rngData = wsData.Cells(1).CurrentRegion
Set wsPT = Worksheets("TCD automatique3" )
'Suppression de tous les TCD existants dans la feuille
For Each pt In wsPT.PivotTables
pt.TableRange2.Clear
Next pt
With wsPT
Set ptCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, rngData, 4)
Set pt = ptCache.CreatePivotTable(wsPT.Range("B12" ), "TCD_1", , 4)
With Sheets("TCD automatique3" ).Activate
Range("B10" ) = "Les salaires annuels bruts en kilo euros par type de formation "
Range("B10" ).Font.Size = 18
Range("B10" ).Font.Italic = True
Range("B10" ).Font.Name = "Arial"
End With
With pt
.ManualUpdate = True
'Ajout d'une Ligne Régime de formation
With .PivotFields("regime_6m" )
.Orientation = xlRowField
.Position = 1
End With
'Ajout d'une Ligne spécialité
With .PivotFields("specialite_6m" )
.Orientation = xlRowField
.Position = 2
End With
' Calcul du saliare moyen
With pt.PivotFields("emploi_salaire_6m" )
.Orientation = xlDataField
.Function = xlAverage
.Position = 1
.NumberFormat = "0.00"
.Name = "Moyenne"
End With
'Calcul du salaire Min
With pt.PivotFields("emploi_salaire_6m" )
.Orientation = xlDataField
.Function = xlMin
.Position = 2
.NumberFormat = "0.00"
.Name = "Min"
End With
' Calcul du salaire Max
With pt.PivotFields("emploi_salaire_6m" )
.Orientation = xlDataField
.Function = xlMax
.Position = 3
.NumberFormat = "0.00"
.Name = "Max"
End With
' Calcul du salaire Median
With pt.PivotFields("emploi_salaire_6m" )
.Orientation = xlDataField
.WorksheetFunction.Median ("emploi_salaire_6m" )
.Position = 4
.NumberFormat = "0.00"
.Name = "Mediane"
End With
.ManualUpdate = False
End With
End With
Set pt = Nothing
Set ptCache = Nothing
Set rngData = Nothing
Set wsPT = Nothing: Set wsData = Nothing
End Sub