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
| Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
'-- 1. PREPARATION
'---- a. Déclaration des variables
Dim pvTable As PivotTable
Dim PvIt As PivotItem
Dim x As Double
Dim Ligne As Integer, Colonne As Integer, ColonneEnd As Integer, LigneEnd As Integer
Dim MensExtraVar As Long
Dim MensP1 As Long, MensP2 As Long, MensP3 As Long, MensLissVar As Long
'---- b. Adressage actualisé du TCD
'Définition du TCD
Set pvTable = Sheets("TCD Supplier 2006").PivotTables("TCD S")
'Definition de la derniere ligne du TCD
Sheets("TCD Supplier 2006").Range("E9").Select
Range(Selection, Selection.End(xlDown)).Select
LigneEnd = Selection.Rows.Count + 8
'Definition de la derniere colonne du TCD
Sheets("TCD Supplier 2006").Range("A9").Select
Range(Selection, Selection.End(xlToRight)).Select
ColonneEnd = Selection.Columns.Count
'-- 2. MISE EN FORME DU TCD
'---- a. Effacement des données précédentes
Sheets("TCD Supplier 2006").Range(Cells(LigneEnd + 1, 1), Cells(LigneEnd + 50, ColonneEnd)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
'---- b. Mise en forme du TCD lui même
'---- c. Mise en forme des lignes calculées
'Mise en forme de la ligne Mensuel Extrapolé
Sheets("TCD Supplier 2006").Rows(LigneEnd).Select
Selection.Copy
Sheets("TCD Supplier 2006").Rows(LigneEnd + 1).Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("TCD Supplier 2006").Cells(LigneEnd + 1, 1) = "Mensuel extrapolé"
'Mise en forme de la ligne Mensuel Lissé
Sheets("TCD Supplier 2006").Rows(LigneEnd + 2).Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("TCD Supplier 2006").Cells(LigneEnd + 2, 1) = "Mensuel lissé"
'-- 3. BOUCLE SUR SEM DES LIGNES CALCULEES EN FIN DE TCD
On Error Resume Next
For Each PvIt In pvTable.PivotFields("SEM").PivotItems
'---- a. Adressage de l'occurence
'Numero de Semaine
x = PvIt.Caption
'Adressage de la colonne ciblée
Colonne = Sheets("TCD Supplier 2006").Range(PvIt.DataRange.Address).Column
'---- b. Calculs des lignes de fin de TCD
'Ligne calculée Mensuel Extrapolé
MensExtraVar = pvTable.GetData("'Cost' 'SEM' 'SEM'['" & x & "']")
Sheets("TCD Supplier 2006").Cells(LigneEnd + 1, Colonne + 1) = (MensExtraVar * 52) / 12
'Ligne calculée Mensuel Lissé
MensP1 = (MensExtraVar * 52) / 12
MensP2 = ((pvTable.GetData("'Cost' 'SEM' 'SEM'['" & x - 1 & "']")) * 52) / 12
MensP3 = ((pvTable.GetData("'Cost' 'SEM' 'SEM'['" & x - 2 & "']")) * 52) / 12
MensLissVar = Application.WorksheetFunction.Average(MensP1, MensP2, MensP3)
Sheets("TCD Supplier 2006").Cells(LigneEnd + 2, Colonne + 1) = MensLissVar
Next
End Sub |
Partager