[RESOLU] Insérer automatiquement des lignes entre 2 TCD

[scoubidou35]

Bonjour à tous,

Nouvelle problématique. Je dois mettre en place un document une sorte de tableau de bords avec des TCD.
L'un en dessous de l'autre avec 3-4 lignes pour séparer les tableaux. C'est pour que ca rentre dans une page A4.

Le problème c'est que maintenant quand je fais une mise à jour le tableau du haut et celui du milieu ont besoins de plus de place. J'ai donc une message erreur à cause du manque de place.

Lors d'une précédente question sur le forum (concernant le rajout de ligne automatiquement dans un tableau)
L'une des propositions me permettait de rajouter des lignes à l'aide d'un bouton qui se décalait à mesure que le tableau grandissait.

N'étant que novice, je m'adresse donc aux pros du code...
Est il possible que lors de l'actualisation des TCD les nombre avoir le même principe.
Je m'explique... quand, je fais une actualisation des 3 TCD en même temps le premier TCD va rajouter le nombre de ligne qu'il a besoin décalant ainsi le second TCD qui fera de même et ainsi de suite.

Voila en gros mon idée. J'espère avoir été assez précis sur mon problème.
Merci à tous

 

[herve62]

Bonsoir
@scoubidou35 : vu maintenant le nbre de post soumis ici , tu devrais savoir qu'il vaut mieux joindre un bout de fichier , non ???

 

[scoubidou35]

Bonsoir herbe62

Je suis désolé. J'ai été un peu trop vite. Voici donc un fichier pour donner un exemple.
J'ai volontairement très serrer les TDC pour provoquer le message.

Et avant qu'on me fasse la remarque . Je sais qu'il vaut mieux mettre les TCD cote à cote plutôt que l'un dessus l'autre
Mais dans mon fichier la BD date de 2012 donc un gros morceau et si l'on veut qu'a l'impression des doc. soit lisible il faut que je travaille et portrait .

Merci

 

[herve62]

Bonjour
Si je me rappelle , l'insertion de lignes se faisait en VBA , là c'est un TCD donc excel pur ,
Loin d'être ma tasse de thé le TCD ne se manipule pas en vba !
Juste pour avoir pris connaissance de ton blocage , la seule raison est que ta selection doit s'afficher à un endroit
ou il existe déjà un résultat de tableau issu d'un filtre TCD et les seules réponses que j'ai pu lire sont de déplacer ( même sur un autre onglet) tes résultats
Mais bon ici , des super barbatruc pourront peut être outre passer ces restrictions ?? et te trouver une soluce !!
PS : le sujet a déjà été évoqué ici sur ce forum entre 2010 et 2012 , sans finalité !!

 

[scoubidou35]

Bonjour,

Oui, c'est bien ça le blocage est lié du fait qu'un tableau ou des cellules fusionnée se trouve au mauvais endroits. J'ai parcourus internet en vain.

Sinon petite question supplémentaire. J'ai créé un bouton dans mon fichier mère pour actualiser UN TCD.

J'ai mis en module :

Sub Actualisation_TCD()
On Error Resume Next
ActiveSheet.PivotTables("TCD").PivotCache.Refresh
End Sub

J'ai rajouté On resume next car sinon ca bug
mais j'ai l'impression que cela ralentie mon fichier donc je voulais avoir votre avis.

Merci

 

[herve62]

Tiens essaye ça , je l'ai mis dans ton code , pas de bug et c'est rapide instantané !!

Sub Actualisation_TCD()
For nb = 1 To ActiveSheet.PivotTables.Count
    ActiveSheet.PivotTables(nb).PivotCache.Refresh
Next
End Sub

 

[job75]

Bonsoir scoubidou35, herve62,

Dans le fichier joint 4 checkboxes pilotent les 4 slicers du segment (masqués).

Avec ce code dans la feuille TABLEAU DE BORD l'espacement entre les TCD est toujours de 1 ligne quel que soit le filtrage :

Private Sub CheckBox1_Click()
Filtre
End Sub

Private Sub CheckBox2_Click()
Filtre
End Sub

Private Sub CheckBox3_Click()
Filtre
End Sub

Private Sub CheckBox4_Click()
Filtre
End Sub

Sub Filtre()
Static flag As Boolean 'mémorise la variable
If flag Then Exit Sub
Dim Ninsert&, espace&, a&(), i&, col%, j&
Ninsert = 100 'à adapter
espace = 1 'nombre de lignes entre les TCD, à adapter
ReDim a(1 To PivotTables.Count)
Application.ScreenUpdating = False
'---insertion de lignes---
For i = 1 To UBound(a): a(i) = PivotTables(i).TableRange1.Row: Next
For i = 1 To UBound(a) - 1: Rows(Application.Large(a, i)).Resize(Ninsert).Insert: Next
'---MAJ---
If CheckBox1 + CheckBox2 + CheckBox3 + CheckBox4 = 0 Then
    flag = True
    CheckBox1 = True: CheckBox2 = True: CheckBox3 = True: CheckBox4 = True
    flag = False
End If
With ThisWorkbook.SlicerCaches("Segment_Région")
    .SlicerItems("EST").Selected = CheckBox1
    .SlicerItems("NORD").Selected = CheckBox2
    .SlicerItems("OUEST").Selected = CheckBox3
    .SlicerItems("SUD").Selected = CheckBox4
End With
'---suppression de lignes---
For i = 1 To UBound(a): a(i) = PivotTables(i).TableRange1.Row: Next
For i = 1 To UBound(a) - 1
    col = PivotTables(i).TableRange1.Column
    For j = Application.Large(a, i) - 1 To 1 Step -1
        If Cells(j - espace, col) = "" Then Rows(j).Delete Else Exit For
Next j, i
End Sub

A+

 

[job75]

Dans le code précédent j'ai revu le cas où CheckBox1 + CheckBox2 + CheckBox3 + CheckBox4 = 0.

 

[scoubidou35]

Bonsoir job
Vraiment trop fort, j'ai regardé le fichier ca fait bien le travail que je recherche. Par contre j'ai un souci car
tu utilises des CheckBox et il en faut 1 pour chaque segment.
Je ne pensais pas qu'il aurait fallu intervenir sur les segments. Du coup mon fichier exemple n'est pas assez précis.
Car dans le vrai fichier il y a 2 filtres et ils sont évolutifs avec le temps
En fait j'ai souvent des filtres "années" donc 1 segment/année et qui continu à grossir et j'ai un filtre "commerciaux" donc 1 segment/personne
qui évolue également dans le temps (des gens qui arrives, d'autres qui partent).
Peut on adapter et remplacer les checkBoxs avec par exemple des combobox ou listbox ou autre ?
Merci.

 

[job75]

Il faut des checkboxes, une pour chaque slicer de chaque segment.

 

[scoubidou35]

Quand je regarde le code, je suis pas un pro loin de là mais j'aime comprendre les codes. Merci d'ailleurs pour les commentaires dans le code pour m'aider à comprendre le fonctionnement.

Les checkbox sont la pour faire une filtration à choix multiple...c'est bien cela? Ils permettent de communiquer avec le TCD

Et si au lieu d'utiliser des segments j'utilise les filtres classiques en mettant "Année" et Commerciaux" dans la zone filtre du TCD
et que j'intègre dans des cellules par exemple B3 pour les "Années" et D3 pour les "Commerciaux" des Listes dynamiques.

Ca ne fonctionnerai pas non plus?
j'ai déjà eu l'occasion d'utiliser un code ou à partir de la valeur sélectionnée dans une liste dynamique je joue sur la filtration.
Ainsi je commande les filtres TCD commandé par VBA relié à un liste dynamique.

Merci

 

[chris]

Bonjour à tous

Une variante :

• on espace les TCD de 100 lignes (ou moins au début avant de filtrer)
  
• à chaque filtre, le code ajoute ou retranche des lignes pour garder 100 lignes et masque 99 lignes

 

[job75]

Bonjour scoubidou35, herve62, chris,

Avec le fichiers (1) précédent l'exécution prenait plus de 3 secondes avec l'insertion de 1000 lignes (Ninsert = 1000).

Avec ce fichier (2) on évite la boucle imbriquée pour la suppression de lignes, l'exécution est immédiate :

'---suppression de lignes---
ReDim a(1 To PivotTables.Count, 1 To 2)
For i = 1 To UBound(a)
    a(i, 1) = PivotTables(i).TableRange1.Row
    a(i, 2) = PivotTables(i).TableRange1.Rows.Count 'hauteur du TCD
Next
For i = 1 To UBound(a) - 1
    j = Application.Large(Application.Index(a, , 1), i + 1)
    j = j + Application.VLookup(j, a, 2, 0) + espace
    Rows(j & ":" & Application.Large(Application.Index(a, , 1), i) - 1).Delete
Next

A+

 

[scoubidou35]

Bonjour chris et job75
Merci pour votre travail.
Je viens de regarder ton travail chris et je suis perdu mais le fait de filtrer directement avec les segments sans les checkbox me plait.
J'ai donc copier/coller ton code directement dans mon fichier dans workbook (donc j'en déduit qu'en faisant ainsi je pourrais intervenir sur des TCD sur d'autres feuilles. Mais j'ai un bug (cf. photo) je ne sais pas pourquoi. Dans cette feuille du dossier j'ai deux filtre et quelques cellules fusionnées pour faire une zone pour signature des chefs, je ne sais pas si ceux sont les causes
Sinon quand je filtre c'est pas instantané (la base de donnée fait pour l'instant 973 lignes)
job75 ton code est plus rapide je dirais même instantané.

Donc vos 2 propositions sont superbe l'un est pratique car on utilise directement les segments et l'autre est rapide.
Est ce qu'il est possible d'adapter le code pour avoir le bénéfice des 2 (souplesse et rapidité d'exécution)?

Merci encore pour l'aide et le temps passé. Je vais essayé d'analyse et de comprendre vos codes et je sens qu'il va me falloir du café
Bon dimanche

 

[chris]

Bonjour

Sans le fichier difficile de dire ce qui pose problème : il faudrait à minima l'état des variables locales.

J'ai dupliqué tes lignes de BD jusqu'à plus de 1100 lignes et c'est instantané chez moi...