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 70 71 72 73 74 75 76 77 78 79 80 81 82 83
|
Sub GenererTcd(LigneTitre As Long, PremiereColonneFeuille As Long)
Dim DerniereLigne As Long
Dim DerniereColonne As Long
Dim AireDuTcd As Range
Dim Pvt As PivotTable
Dim Sh As Worksheet
Dim ShEnCours As Worksheet
Dim ShTcd As Worksheet
Set ShEnCours = ActiveSheet
' Recherche et suppression du Tcd existant
For Each Sh In Sheets
If Sh.Name = "Tcd Regroupement" Then
Application.DisplayAlerts = False
Sheets("Tcd Regroupement").Delete
Application.DisplayAlerts = False
End If
Next Sh
ShEnCours.Activate
DerniereColonne = Cells(LigneTitre, ActiveSheet.Columns.Count).End(xlToLeft).Column
DerniereLigne = Cells(ActiveSheet.Rows.Count, PremiereColonneFeuille).End(xlUp).Row
' Recherche de l'aire du tableau
Set AireDuTcd = Range(Cells(LigneTitre, PremiereColonneFeuille), Cells(DerniereLigne, DerniereColonne))
AireDuTcd.Select
Sheets.Add
Set ShTcd = ActiveSheet
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
AireDuTcd.Address, Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:=ShTcd.Cells(3, 1), TableName:="Tcd_Regroupement", DefaultVersion:=xlPivotTableVersion10
Cells(3, 1).Select
Set Pvt = ShTcd.PivotTables("Tcd_Regroupement")
With Pvt.PivotFields("Référence")
.Orientation = xlRowField
.Position = 1
End With
With Pvt
.AddDataField Pvt.PivotFields("Pièce"), " Pièce", xlSum
.AddDataField Pvt.PivotFields("Moteur"), " Moteur", xlSum
.AddDataField Pvt.PivotFields("Hélice"), " Hélice", xlSum
.PivotFields(" Pièce").NumberFormat = "# ##0"
.PivotFields(" Moteur").NumberFormat = "# ##0"
.PivotFields(" Hélice").NumberFormat = "# ##0"
End With
With Pvt.DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.Name = "Tcd Regroupement"
ActiveWorkbook.ShowPivotTableFieldList = False
Set Pvt = Nothing
Set ShTcd = Nothing
Set AireDuTcd = Nothing
Set ShEnCours = Nothing
End Sub |
Partager