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
| '--------- début -----------------------------
CreationTableau 1, 1, choixstat.ComboBox1.Value, 220, 5, "tableau", "Graphique 1"
Worksheets(1).Select
Range("A1").Select
Worksheets("resultat").Select
ActiveWindow.Zoom = 160
ActiveWindow.SmallScroll ToRight:=3
ActiveWindow.SmallScroll Down:=6
Application.ScreenUpdating = True
Unload choixstat
Unload choix
End Sub
Sub CreationTableau(posLeft As Single, posTop As Single, donnee As String, _
LeftGraph As Single, TopGraph As Single, nom As String, graphique As String)
Dim PTCache As PivotCache
Dim PT As PivotTable
Sheets("BDD").Select
'----------------------------------------------------------------------------
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
Set PT = PTCache.CreatePivotTable _
(TableDestination:="resultat!R" & posTop & "C" & posLeft, _
TableName:="graph")
With PT
.PivotFields("date").Orientation = xlRowField
.PivotFields("Nom d'ordre").Orientation = xlPageField
.PivotFields(donnee).Orientation = xlDataField
End With
On Error Resume Next
With Worksheets("resultat").PivotTables("graph").PivotFields("Nombre de " & donnee)
.Function = xlAverage
End With
On Error Resume Next
With Worksheets("resultat").PivotTables("graph").PivotFields("Somme de " & donnee)
.Function = xlAverage
End With
Worksheets("resultat").Shapes.AddChart2(LeftGraph, xlColumnClustered).Select
ActiveChart.SetSourceData source:=Worksheets("resultat").Range(Range("A1").Offset(0, posLeft - 1), Range("A1").Offset(0, posLeft).End(xlDown).End(xlDown))
'----------------------------------------------------------------------------
End Sub |
Partager