1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| Sub CreatePivotdev()
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Data").Select
Range("A1").Select
With ActiveSheet.PivotTables("Tableau croisé dynamique12").PivotFields( _
"Qualification")
.PivotItems("Apport vente ").Visible = False
.PivotItems("Fin suspension fact. ").Visible = False
.PivotItems("Hausse tarifaire ").Visible = False
.PivotItems("Perte ").Visible = False
.PivotItems("Perte partielle ").Visible = False
.PivotItems("Rénégo tarifs ").Visible = False
.PivotItems("Suspension fact. ").Visible = False
.PivotItems("Transfert inter-centre ").Visible = False
.PivotItems("Variation d'activité ").Visible = False
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique12").PivotFields("Motif")
.PivotItems("Cessation d'activité ").Visible = False
.PivotItems("Changement de propriétaire ").Visible = False
.PivotItems("Concurrence ").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("Tableau croisé dynamique12").PivotFields( _
"Qualification").Subtotals = Array(False, False, False, False, False, False, False, _
False, False, False, False, False)
Range("D12").Select
ActiveSheet.PivotTables("Tableau croisé dynamique12").PivotFields("Motif"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Set objTable = Data.PivotTableWizard
Set objField = objTable.PivotFields("Services")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Qualification")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Motif")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Affecté à")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("CA")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##0.00 $"
End Sub |
Partager