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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
| If CheckBox1.Value Then
Call Support
If CheckBox3.Value Then
Dim pc1 As PivotCache
Dim pt1 As PivotTable
Dim donnees As Range
Dim num_ligne As Integer
Dim posTab As Integer
'Tableau Dynamique de l'onglet support
ActiveWorkbook.Sheets("Support").Select
ActiveSheet.Cells(1, 1).Select
Selection.End(xlDown).Select
num_ligne = ActiveCell.Rows.row
posTab = num_ligne + 10
Set donnees = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(num_ligne, 15))
Set pc1 = ActiveWorkbook.PivotCaches.Add(xlDatabase, "'" & donnees.Parent.Name & "'!" & donnees.Address(ReferenceStyle:=xlR1C1))
Set pt1 = pc1.CreatePivotTable(TableDestination:=Worksheets("Support").Cells(posTab, 1), _
TableName:="Tableau1", DefaultVersion:=xlPivotTableVersion10)
With ActiveSheet.PivotTables("Tableau1").PivotFields("Status")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Tableau1").PivotFields("Company")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tableau1").PivotFields("Module")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau1").AddDataField ActiveSheet.PivotTables( _
"Tableau1").PivotFields("Calipso No"), "Somme de Calipso No", xlCount
With ActiveSheet.PivotTables("Tableau1").PivotFields("Status")
.PivotItems("Closed").Visible = False
End With
End If
ActiveWorkbook.Sheets("Support").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:="<>Closed", Operator:=xlAnd
Call Figer
If CheckBox6.Value Then
' Graphe de l'onglet support
num_ligne = ActiveCell.Rows.row
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Support").Range("A" & posTab & ":E" & num_ligne)
ActiveChart.Location Where:=xlLocationAsObject, Name:="Support"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Total"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.PivotLayout.PivotTable.PivotFields("Status")
.PivotItems("Closed").Visible = False
End With
End If
MsgBox "Restitution Support terminée"
End If |
Partager