Sub Capacité()
'
' Capacité Macro
' Macro enregistrée le 16/06/2009 par Moi
'
' Touche de raccourci du clavier: Ctrl+Maj+C
'Préparation du TCD
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!A:K").CreatePivotTable TableDestination:="", TableName:= _
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddFields RowFields:= _
Array("Capacité"), ColumnFields:="Lieux", PageFields:= _
Array("Année", "Mois", "Jour", "Heure")
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Papiers"). _
Orientation = xlDataField
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect "", _
xlDataAndLabel, True
'Fin de la préparation du TCD
'Mise en page
'Masquer la ligne et la colonne vides :
Range("A11").Select
Selection.Delete
Range("A1,A2,A3,A4,A6,A7,B6").Select
Selection.Interior.ColorIndex = 2
Selection.Font.FontStyle = "Gras"
Selection.Font.Size = 9
Range("A6,A7").Select
Selection.HorizontalAlignment = xlCenter
'Mettre en violet les 2 lignes
Range("B7,C7,D7,E7,F7,A11,B11,C11,D11,E11,F11").Select
Selection.Interior.ColorIndex = 24
Selection.Font.ColorIndex = 55
Selection.Font.FontStyle = "Gras"
Selection.HorizontalAlignment = xlCenter
Range("F8:F10").Select
Selection.Font.FontStyle = "Gras"
Selection.HorizontalAlignment = xlCenter
Range("A8:E10").Select
Selection.HorizontalAlignment = xlCenter
Range("A8:A10,F8:F10,B8:E10").Select
Selection.Interior.ColorIndex = 2
Range("A11,B11,C11,D11,E11,F11").Select
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 55
End With
'Mettre "Année", "Mois", "Jour" et "Heure" dans l'ordre
Range("A4").Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Année")
.Orientation = xlPageField
.Position = 4
End With
Range("A4").Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Mois")
.Orientation = xlPageField
.Position = 3
End With
Range("A4").Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Jour")
.Orientation = xlPageField
.Position = 2
End With
'Créer le graphique
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Feuil1").Range("B10")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.PlotArea.Select
Selection.Border.ColorIndex = 16
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
End With
ActiveChart.ChartType = xlColumnClustered
On Error Resume Next
ActiveChart.SeriesCollection(2).Interior.ColorIndex = 24
ActiveChart.SeriesCollection(3).Interior.ColorIndex = 20
ActiveChart.SeriesCollection(4).Interior.ColorIndex = 37
End Sub