Discussion :

[Jika971]

Bonjour,

J'ai créer un tableau croisé dynamique sur Excel à l'aide en VBA. J'arrive donc à deux soucis majeurs.
Ayant utilisé la fonction différence par rapport à %, je me retrouve avec un surplus de colonne inutile.
Je souhaite donc masqué toutes les colonnes sauf les deux dernières . J'ai essayé d'écrire un code mais il ne risque pas de marché pour tous les cas de figure.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
For Each c In Range("9:9").Cells
        If c.Value = "01" Then
            c.EntireColumn.Hidden = True
        End If
    Next c

Le second problème est que je souhaite grisé la dernière colonne. J'ai aussi écris un code, mais avec ce dernier si on filtre des données, il ne grise pas la dernière colonne.

Voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
With ActiveSheet.PivotTables(1).TableRange1
        Set rng = .Offset(1, .Columns.Count - 1).Resize(.Rows.Count - 1, 1)
    End With
    rng.Select
    Selection.Interior.Color = RGB(214, 214, 214)

Quelqu'un pourrait t-il m'éclairer davantage ?

Je laisse une image pour que ce soit plus compréhensible :

[Invité]

Bonjour,

A tester :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Option Explicit
 
Sub ModifierLeDataBodyRange()
 
Dim Pvt As PivotTable
Dim I As Integer
 
    Set Pvt = ActiveSheet.PivotTables(1)
    With Pvt.DataBodyRange
         '.Columns.Hidden = False
         If .Columns.Count > 2 Then
            For I = .Columns.Count - 2 To 1 Step -1
                .Columns(I).Hidden = True
            Next I
         End If
    End With
    Set Pvt = Nothing
 
End Sub

Quant à la mise en forme de la couleur de la colonne Total, essayez de créer un nouveau style de mise en forme du TCD (avec ou sans l'enregistreur de macro). Vous aurez accès aux différentes parties du TCD et vous pourrez en modifier leur format.


Pièce jointe 362444

[78chris]

Bonjour à tous

Pour le second point faisable par une MFC spécifique TCD : se placer sur la cellule correspondant à la 1ère ligne, 1ère colonne pour ce calcul (D11 à priori), appliquer la MFC au champ % Augmentation (et non à la sélection) avec une formule du type =ET(D$10="% Augmentation"; D$9=MAX(9:9))

Cependant dans un TCD, il suffit de se placer pile au dessus d'un intitulé % Augmentation pour faire apparaître une petite flèche noire, de cliquer pour sélectionner toutes les colonnes % Augmentation et les colorer.
Dans le mesure où elle sont masquées, seule la couleur de la dernière se verra.

Utiliser l'enregistreur macro pour le code...

[Jika971]

Bonjour,


Merci de votre réponse a vous deux, elle m'as énormement aidé parcontre le petit bémol est que si j'ajoute un filtre elle ne marche pas.
Par exemple si j'ajoute l'année 2017 manuellement (où il y a plus de colonne)
Ici :
L'affichage des 4 dernière colonnes ne fonctionne pas, elle affiche toute les colonnes.

Voici la construction de mon code VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
Sub PourcentageAugmentation()
Application.ScreenUpdating = False
 
'Declaration des variables
Dim Pvt As PivotTable
Dim I As Integer
Dim rng As Range
Set Pvt = ActiveSheet.PivotTables("Tableau croisé dynamique1")
 
 
    ' Fonction qui réinitialise le TCD et supprimes tout les filtre
    HideFields Pvt
 
        ' Création du TCD
        With Pvt.PivotFields("Accord discount")
            .Orientation = xlPageField
            .Position = 1
        End With
        With Pvt.PivotFields("Année")
            .Orientation = xlColumnField
            .PivotItems("2017").Visible = False
            .PivotItems("(blank)").Visible = False
            .Position = 1
        End With
        With Pvt.PivotFields("Mois")
            .Orientation = xlColumnField
            .Position = 2
        End With
        With Pvt.PivotFields( _
            "Direction")
            .Orientation = xlRowField
            .Position = 1
        End With
        With Pvt.PivotFields("Pays")
            .Orientation = xlRowField
            .Position = 2
            .AutoSort xlDescending, "% Augmentation"
        End With
        Pvt.AddDataField ActiveSheet. _
            PivotTables("Tableau croisé dynamique1").PivotFields( _
            "Settlment avec CN (Net Charge)"), "Nombre de Settlment avec CN (Net Charge)", _
            xlCount
        With Pvt.PivotFields( _
            "Nombre de Settlment avec CN (Net Charge)")
            .Caption = "Somme de Settlment avec CN (Net Charge)"
            .Function = xlSum
            .NumberFormat = "# ##0 €"
        End With
        Pvt.AddDataField ActiveSheet. _
            PivotTables("Tableau croisé dynamique1").PivotFields( _
            "Settlment avec CN (Net Charge)"), "Nombre de Settlment avec CN (Net Charge)", _
            xlCount
        With Pvt.PivotFields( _
            "Nombre de Settlment avec CN (Net Charge)")
            .Caption = "% Augmentation"
            .Function = xlSum
            .NumberFormat = "# ##0 €"
        End With
        With Pvt.PivotFields( _
            "% Augmentation")
            .Calculation = xlPercentDifferenceFrom
            .BaseField = "Mois"
            .BaseItem = "(précédent)"
            .NumberFormat = "0,0%"
        End With
 
 
    ' Mise en forme de la ligne 10
    Range("10:10").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .ReadingOrder = xlContext
        .RowHeight = 50
    End With
 
    'Redimensionne les colonne de C à Z
    Columns("C:Z").ColumnWidth = 17
 
    'Masque les colonnes si le nombre de colonne du TCD est supérieur (garde les 4 dernières)
    With Pvt.DataBodyRange
        ' .Columns.Hidden = False
         If .Columns.Count > 4 Then
            For I = .Columns.Count - 4 To 1 Step -1
                .Columns(I).Hidden = True
            Next I
         End If
    End With
 
    ActiveSheet.PivotTables("Tableau croisé dynamique1").HasAutoFormat = False
 
     ' Libère la variable
     Set Pvt = Nothing
     Set rng = Nothing
 
     ' Sélectionne la cellule A1
     Range("A1").Select
 
Application.ScreenUpdating = True
End Sub

Voyez vous d'où viens l'erreur ?

[78chris]

Bonjour

Je n'ai pas détaillé le code mais a priori je partirais de la droite du TCD (repérer la colonne terminant le databodyrange) -1 ou n colonnes avec un boucle décroissante testant le titre et si celui-ci est % augmentation, masquage...

[Invité]

J'en profite pour saluer Chris au passage.

Sinon, combien y a-t-il de colonnes dans la partie DataBodyRange. Que donne le Debug.print que j'ai inséré dans cette partie de code ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
 'Masque les colonnes si le nombre de colonne du TCD est supérieur (garde les 4 dernières)
    With Pvt.DataBodyRange
        ' .Columns.Hidden = False
         Debug.print   .Columns.Count 
         If .Columns.Count > 4 Then
            For I = .Columns.Count - 4 To 1 Step -1
                .Columns(I).Hidden = True
            Next I
         End If
    End With

[Jika971]

Il y en a 28