Insérer des lignes sous condition de lignes qui se rajoutent dans un TCD

**Waragnac** · 29/12/2016, 09h46

Bonjour tout le monde,

J'ai quatre TCD sur une même page, l'un à la suite de l'autre (Voici le document en question : InsertLignes.xlsm).
J'ai créé une plage comme source de données qui va automatiquement prendre en compte d'éventuelles nouvelles données qui pourraient s'ajouter en "Feuil1".
Les TCD vont prendre en compte ces données et donc une ou plusieurs lignes vont s'ajouter (par exemple si un nouveau nom arrive dans la Feuil1).
Le but de la manœuvre serait d'insérer en dessous de chaque TCD le même nombre de lignes qui s'ajoute à chaque TCD quand il y a des ajouts qui sont faits en Feuil1, afin d'éviter que les TCD se télescopent.
J'ai réussi à programmer l'insertion de ligne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub InsertLigne()
 
Dim i As Long
 
For i = 1 To 30
  If Cells(i, 1) = "Total général" Then
   i = i + 1
     Row(i).Select
       Selection.Insert shift:=xlDown
  End If
Next i
 
End Sub

Code :

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

1
2
3
Private Sub Worksheet_Activate()
InsertLigne
End Sub

Le problème est que je n'arrive pas à coder la condition : "si tant de lignes s'ajoutent à tel TCD alors tant de lignes s'insèreront en dessous de "Total général"".

J'en appelle à vos brillantes lumières

Merci par avance.

Cdt

**halaster08** · 29/12/2016, 10h49

Bonjour,

Plutot que de compter ceux qui ont été rajouté, il me semble plus simple de compter la taille que prendra ton TCD apres la mise a jour, en comptant le nombre de valeurs unique sur la variable que tu met en ligne (si tu as plusieurs c'est un peu plus compliqué)

Une fois ce nombre obtenu tu le compare avec le nombre de lignes qui sépare tes TCD, et tu ajoutes/enlèves des lignes suivant la valeur obtenu.
Pour voir le nombre de ligne entre tes TCD (a adapter):

Code :

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

ActiveSheet.PivotTables("PivotTable1").TableRange2.Row -ActiveSheet.PivotTables("PivotTable2").TableRange2.Row

**Waragnac** · 29/12/2016, 11h34

Bonjour,

Merci pour ta réponse.

Dans l'application j'ai du mal à saisir... Est-ce qu'il faut que je déclare par exemple une constante qui serait le nombre de lignes entre les deux TCD ?

Merci d'avance.

Cdt

**halaster08** · 29/12/2016, 11h53

Tu peux mais tu n'ai pas obligé
Que tu codes :

Code :

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

if ActiveSheet.PivotTables("PivotTable2").TableRange2.Row -ActiveSheet.PivotTables("PivotTable1").TableRange2.Row < X then

ou

Code :

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

1
2
 toto = ActiveSheet.PivotTables("PivotTable2").TableRange2.Row -ActiveSheet.PivotTables("PivotTable1").TableRange2.Row
if toto < X then

C'est la même chose.

**Waragnac** · 29/12/2016, 12h12

Le truc c'est que quand j'utilise ta formule avec un MsgBox, le nombre affiché est toujours le même quand une ou plusieurs lignes s'ajoutent dans les tableaux, car la formule semble calculer la différence entre la dernière ligne du TCD2 et la dernière ligne du TCD1, alors que ce qu'il faudrait c'est que le nombre de cellules vides entre les deux tableaux reste le même en tenant compte des mises à jours et des lignes qui s'ajoutent dans les deux tableaux.
Je ne sais pas si je suis très clair...

**78chris** · 29/12/2016, 12h18

Bonjour

Une autre solution, moyennement propre mais simple est de créer un TCD ailleurs, basé sur les colonnes complètes de la source, avec juste le champ qui donne le nombre de lignes des autres, et d'actualiser celui-ci avant d'actualiser les autres TCD : tu auras l'indication du nombre de lignes nécessaires.

Quand à la source, plutôt que DECALER tu peux la mettre sous forme de tableau et baser tes autres TCD sur le tableau...

**halaster08** · 29/12/2016, 12h42

ActiveSheet.PivotTables("PivotTable2").TableRange2.Row donne la premiere ligne du TCD
La différence de ces deux valeurs de te donne le nombres de lignes qui séparent tes deux TCD
Admettons que tu obtienne 10
Sur ces 10 lignes tu dois mettre: les éventuels filtres(nb fixe) les entetes(nb fixe) les données(nb variable) les totaux(nb fixe) et l'espace entre les tcd(nb fixe).
Supposons que tu n'ai pas de filtre 1ligne d'entete et 1 grand total et 2 lignes d'espace. Ca te laisse 6 lignes pour les données.
Compte dans ta source le nb de données unique que tu auras en ligne (disons X), si X<6 pas de soucis, sinon tu dois insérer X-6 lignes

Code :

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

1
2
3
4
5
6
7
8
9
Sub test()
X = 12 'a toi de calculer le nb de lignes dont a besoin
dist = ActiveSheet.PivotTables("PivotTable2").TableRange2.Row - ActiveSheet.PivotTables("PivotTable1").TableRange2.Row
If dist < (X + 1 + 1 + 2) Then
Rows(ActiveSheet.PivotTables("PivotTable2").TableRange2.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'attention je ne passe qu'une ligne ici, a toi d'adapter
End If
ActiveSheet.PivotTables("PivotTable1").Refresh
End Sub

Invité · 29/12/2016, 13h29

Envoyé par Waragnac

Bonjour à tous,

Je viens un peu en retard. Le principe de ma solution repose sur celui d'Halaster08. Pour cela j'utilise une variable dico pour lister les TCD et leur positionnement avant modification.
Cette solution nécessite de cocher la référence Microsoft Scripting Runtime dans l'éditeur 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
 
Sub DeplacementTcd(ByVal OngletTcd As Worksheet, ByVal LignePremierTcd As Long, ByVal LignesEntreLesTcd As Long)
 
' Nécessite la référence Microsoft Scripting Runtime
 
Dim ListeDesTcd As New Scripting.Dictionary
Dim ListeCle As Variant, ListeElement As Variant
Dim Tempo1, Tempo2
Dim I As Integer, J As Integer, NombreDeTcd As Integer
Dim LigneEncours As Long, ColonneTcd As Long
 
 
    With OngletTcd
 
         NombreDeTcd = .PivotTables.Count
 
         If Not ListeDesTcd Is Nothing Then
            Set ListeDesTcd = Nothing
         End If
 
         LigneEncours = LignePremierTcd
        For I = 0 To NombreDeTcd - 1
 
            ' Ajout des noms des TCD et des dernières lignes
            '-----------------------------------------------
            If Not ListeDesTcd.Exists(.PivotTables(I + 1).Name) Then
                   ListeDesTcd.Add (.PivotTables(I + 1).Name), .PivotTables(I + 1).TableRange2.Row
            End If
         Next I
 
         ListeCle = ListeDesTcd.Keys
         ListeElement = ListeDesTcd.Items
 
         ' Tri des Tcd par ordre croissant des dernières lignes
         '-----------------------------------------------------
         For I = 0 To ListeDesTcd.Count - 2
             For J = I + 1 To ListeDesTcd.Count - 1
                 If ListeElement(I) > ListeElement(J) Then
 
                       Tempo1 = ListeCle(J)
                       Tempo2 = ListeElement(J)
 
                       ListeElement(J) = ListeElement(I)
                       ListeCle(J) = ListeCle(I)
 
                       ListeCle(I) = Tempo1
                       ListeElement(I) = Tempo2
 
                 End If
 
              Next J
 
         Next I
 
         ' Positionnement des TCD sur l'onglet
         '------------------------------------
              For J = LBound(ListeCle) To UBound(ListeCle)   ' 0 To ListeCle(I) .Count - 1
                  With .PivotTables(ListeCle(J)).TableRange2
                    ColonneTcd = .Column
                    .Cut
                    OngletTcd.Paste OngletTcd.Cells(LigneEncours, ColonneTcd)
                    LigneEncours = LigneEncours + .Rows.Count + LignesEntreLesTcd
                   End With
              Next J
 
         .Cells(LignePremierTcd, ColonneTcd).Activate
 
         Set ListeDesTcd = Nothing
 
    End With
 
End Sub

Et pour mettre en oeuvre cette procédure, il faut simplement :

- Lancer une première fois la macro en définissant la ligne du premier TCD, le nombre de lignes avant actualisation.
- Actualiser les TCD (non traité ici).
- Rejouer la macro en définissant la ligne du premier TCD et le nombre de lignes après actualisation.

Code :

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

1
2
3
4
5
6
7
8
 
Sub EssaiDeplacementTcd()
 
    DeplacementTcd Sheets("Essai"), 2, 1000 ' Plus si vous voulez -)
    ' Actualisation des Tcd
    DeplacementTcd Sheets("Essai"), 2, 2
 
End Sub

**Waragnac** · 29/12/2016, 13h38

Tout d'abord, merci pour vos réponses

J'ai réussi à comprendre quelle serait pour moi la meilleure manière de procéder mais je n'arrive pas à mettre ça en forme, la faute à un manque de connaissances en VBA.
En fait l'idéal serait de calculer le nombre de cellules vides entre la dernière ligne du TCD1 et la première ligne du TCD2 une première fois (nbcellvides1)
Eusuite on lance une boucle avec i = 1 to Range("A" & Rows.Count).End(xlUp).Row pour balayer toutes les lignes du TCD1
On actualise une première fois les deux tableaux, puis on recalcule le nombre de cellules vides qui aura diminué après l'actualisation (nbcellvides2)
La je pose mes conditions : Si nbcellvides2<nbcellvides1 et Cells(i,1)="Total général" (on arrive donc au bout du tableau) alors on calcule la différence entre les deux nombres et on insère le même nombre de lignes après la dernière ligne de TCD1
Ainsi les tableaux pourront s'alimenter sans s'entrechoquer.

Quelqu'un pourrait m'aider à le transposer en code ? J'ai déjà fait le code ci-dessous mais je sais qu'il est bourré d'erreurs

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
Sub InsertLigne()
 
Dim i As Long
Dim nblignes As Long
Dim nblignes2 As Long
Dim nblignes3 As Long
 
 
 
nblignes = WorksheetFunction.Counblank(Cells(7, 1), Cells(12, 1))
 
 
 
 For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
  ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
  ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
   Cells(6, 12).Value = Application.WorksheetFunction.Counblank(Cells(7, 1), Cells(12, 1))
   nblignes2 = Cells(6, 12)
    If nblignes2 < nblignes And Cells(i, 1) = "Total général" Then
    i = i + 1
    nblignes3 = nblignes1 - nblignes2
     Rows("i:i+nblignes3").Select
       Selection.Insert shift:=xlDown
    End If
 Next i
 
End Sub

Merci à vous.

Invité · 29/12/2016, 13h43

Envoyé par Waragnac

Faites l'essai avec le code que j'ai mis en ligne et dites-moi s'il fonctionne correctement. Votre sujet est intéressant.

Cordialement.

**halaster08** · 29/12/2016, 14h01

Envoyé par Eric KERGRESSE

Faites l'essai avec le code que j'ai mis en ligne et dites-moi s'il fonctionne correctement. Votre sujet est intéressant.

Cordialement.

Moi je l'ai testé ça marche très bien, +1

Cette solution nécessite de cocher la référence Microsoft Scripting Runtime dans l'éditeur VBA.

Pour l'activer il faut aller dans le menu VBA Tools -> References

**joe.levrai** · 29/12/2016, 14h14

Bonjour,

pour éviter de devoir cocher une référence permettant d'accéder nativement aux objets dictionnary, on peut travailler en liaison tardive

remplacer :

Code :

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

Dim ListeDesTcd As New Scripting.Dictionary

par :

Code :

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

1
2
Dim ListeDesTcd As Object
Set ListeDesTcd = CreateObject("Scripting.Dictionary")

**Waragnac** · 29/12/2016, 15h08

Merci à vous pour ce code

Je ne connaissais pas du tout cette structure, du coup je suis complètement largué sur la procédure ^^

Par contre ça ne marche pas, il y a une erreur d'exécution 1004 sur la ligne suivante :

Code :

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

.Cells(LignePremierTcd, ColonneTcd).Activate

Invité · 29/12/2016, 15h22

Envoyé par joe.levrai

Bonjour Joe,

Tu as raison, c'est sans doute plus simple.

Envoyé par halaster08

Merci Halaster08 pour ton test.

Envoyé par Waragnac

Ce lien vous donne les différentes zones utilisables d'un TCD : travail-zones-nommees-tcd-vba

Cordialement.

Invité · 29/12/2016, 15h35

Envoyé par Waragnac

Je ne connaissais pas du tout cette structure, du coup je suis complètement largué sur la procédure ^^

Regardez le fichier sur lequel j'ai créé ce code. Module Module_DeplacementTcd

Pièce jointe 231112

Cordialement.

**Waragnac** · 29/12/2016, 16h18

Merci à vous tous, j'essaierai de me débrouiller avec toutes ces informations.

Au cas où, si quelqu'un est intéressé par l'idée de m'aider à formaliser la procédure dont j'ai parlé quelques messages plus haut, avec le comptage de cellules vides, je suis preneur

Invité · 30/12/2016, 09h22

Bonjour,

J'ai créé un billet dans mon blog suite à ce messge mettre-jour-tableaux-croises-dynamiques-jointifs-ajout-lignes-colonnes.

Dans la version présente sur le blog, j'en ai profité pour :

- Rendre le code utilisable en déplacement horizontal également en tenant compte de la remarque de Joe pour l'initialisation de la variable dictionnaire.
- Rafraîchir les TCD.
- Repositionner les TCD en 1 seule passe.

Le code fonctionne bien sur le fichier exemple mais il est possible que je n'ai pas pensé à tout.

Pour M. Waragnac, l'utilisation de l'outil se résume à une seule une ligne de commande : Définition de l'onglet, intervalle entre les tableaux, type de positionnement Vertical ou Horizontal.

Code :

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

1
2
3
4
5
6
7
 
Sub EssaiDeplacementTcd()
 
    DeplacementTcd Sheets("Essai vertical"), 1, "Vertical"
    DeplacementTcd Sheets("Essai horizontal"), 0, "Horizontal"
 
End Sub

Si vous avez des difficultés à mettre en oeuvre cet outil, je vous propose d'essayer sur un de vos fichiers.

Cordialement.

**halaster08** · 30/12/2016, 10h37

Une petite question Eric:

Si j'ai bien tout compris c'est le passage ci-dessous qui déplace les TCD "assez loin" avant de faire la mise à jour:

Code :

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

1
2
3
4
5
6
                         With .PivotTables(ListeCle(J)).TableRange2
                              ColonneEnCours = ColonneEnCours + .Columns.Count + 100
                              LigneEncours = LigneEncours + .Rows.Count + 1000
                              .Cut
                              OngletTcd.Paste OngletTcd.Cells(LigneEncours, ColonneEnCours)
                         End With

Le +1000 lignes tu l'avais mis en variable dans ton précédent code, pourquoi l'avoir mis en dur cette fois?

Invité · 30/12/2016, 10h47

Envoyé par halaster08

Bonjour Halaster08,

Parce que cela n'a plus d'importance pour l'utilisateur car les TCD sont rafraîchis et repositionnés en même temps. Je décale en colonnes et en lignes pour ne pas avoir de chevauchement.
Si tu reprends l'ancienne version et si tu décides par erreur de déplacer d'un nombre de lignes inférieur à la hauteur du TCD à la première passe, la programme plante.
Dans cette nouvelle version, tu as juste à indiquer l'espacement désiré au final.

Cordialement.