VBA pour adapter une présentation en fonction longueur d'un TDC

Bonsoir à tous,

Je souhaite vous présenter mon problème avec une présentation comprenant des TDC.

Je ne crois pas avoir vu de sujet similaire sur le forum... enfin, j'ai essayé de chercher.

Voici mon problème :

Je souhaite rédiger un bilan qui comprendra plusieurs TDC qui vont se suivent. Ce bilan (élaboré sur excel) servira de modèle (donc l'ordre de la présentation ne changera pas). Par contre, je vais modifier les données sources et mettre à jour à chaque fois les TDC. Je ne me pas pas de soucis à ce niveau car j'ai vu que la communauté à proposé plusieurs solutions pour actualiser par un VBA les TDC.

Mon réel problème est que quand je modifie le tableau source et que je fais des mises à jour, les TDC sont bien modifié MAIS si le TCD est plus long (parce que mon tableau source comprend plus d'information) alors le TDC s'affiche au dessus de ma présentation.

Dans mon exemple : mes titres sont en ligne 2 et 14 (onglet "Récapitulatif") - pas de problème avec un TDC de 5 prénoms (Feuil1).

Si mon tableau source comprend 8 prénoms (Feuil2) la ligne 14 est dissimulée sous le TDC actualisé (onglet "Récapitulatif bis").... et là, c'est la galère

Y aurait-il une solution? est-il possible qu'un VBA adapte mes titres en fonction de la longueur du TDC quand il est actualisé?

Merci par avance pour votre aide

Bonne fin d'après-midi à tous !

Petitejiji

Bonjour,

Si VBA vous est autorisé, le principe est d'ajouter des lignes entre les deux TCD avant leur actualisation, puis de supprimer les lignes en trop. Il vous faut également transformer vos tableaux en tableaux structurés pour ne pas avoir à modifier l'aire de la source de vos TCD.

L'exemple ci-dessous concerne votre onglet Récapitulatif bis.

Sub ActualiserLesTcd()

Dim I As Integer, LignePvtGenre As Integer, LignePvtAge As Integer
Dim PvtAge As PivotTable, PvtGenre As PivotTable

    With Sheets("Récapitulatif bis")

         Set PvtAge = .PivotTables("Tcd_Age")
         Set PvtGenre = .PivotTables("Tcd_Sexe")

         LignePvtGenre = PvtGenre.TableRange2.Row - 2
         .Range(.Cells(LignePvtGenre, 1), .Cells(LignePvtGenre + 30, 1)).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

         PvtGenre.PivotCache.Refresh
         PvtAge.PivotCache.Refresh

         LignePvtGenre = PvtGenre.TableRange2.Row

         With PvtAge
              LignePvtAge = .TableRange2.Row + .TableRange2.Rows.Count - 1
         End With

         For I = LignePvtGenre - 4 To LignePvtAge + 2 Step -1
            .Cells(I, 1).EntireRow.Delete
         Next I

    End With

    Set PvtAge = Nothing
    Set PvtGenre = Nothing

End Sub

bonjour Eric Kergresse, Peitejiji,

n'est -ce pas plus facile d'ajouter de lignes entre les 2 TCD de manière que "2 - Notes en fonction du genre" est par exemple la ligne 101. Alors on n'a qu'à cacher les lignes à partir de 2 lignes en dessous le premier TCD jusqu'à la ligne 100.

Bonsoir Eric Kergresse et BsAlv,

@BsAlv : merci pour ton message. En effet, j'y avais pensé mais j'ai beaucoup de bilan à faire (c'est vrai que je n'avais pas précisé cet élément dans ma demande )... je pense que j'y aurai passé quelques nuits à ajouter des lignes entre 2 TDC

@Eric : merci infiniment pour la proposition... cela fonctionne super bien et cela va me faire gagner beaucoup, beaucoup de temps. Vous êtes trop fort ... en plus, le conseil de transformer mes tableaux en tableaux structurés, c'est nickel !

Merci d'avoir pris le temps de vous pencher sur mon problème....qui est maintenant résolu !

Bonne soirée à tous les 2,

Petitejiji

re,

je pense que j'y aurai passé quelques nuits à ajouter des lignes entre 2 TDC

Const Nombre_de_Lignes = 50

Sub Distance_Fixe()
     Dim aLigne(), i, Ligne1, Ligne2, r, x, Derligne
     ReDim aLigne(0)

     With Sheets("récapitulatif bis")        'cette feuille
          For Each pvt In .PivotTables       'boucle les TCDs
               If pvt.TableRange2.Column = 1 Then     'commence en colonne A
                    If Len(aLigne(0)) > 0 Then ReDim Preserve aLigne(UBound(aLigne) + 1)     'si élément 0 n'est plus vide, incrementer le nombre d'éléments
                    aLigne(UBound(aLigne)) = pvt.TableRange2.Row     '1ere ligne du TCD
               End If
          Next

          For i = 0 To UBound(aLigne) - 1    'boucler du 1er jusqu'au avant-dernier TCD (en position du 1ere ligne)
               Ligne1 = WorksheetFunction.Small(aLigne, i + 1)     '1ere ligne du TCD
               Ligne2 = WorksheetFunction.Small(aLigne, i + 2)     '1ere ligne du TCD suivant
               r = Application.Match(Ligne1, aLigne, 0)     'l'index du 1er TCD
               Derligne = .PivotTables(r).TableRange2.Rows.Count + Ligne1     'ligne suivante après ce 1er TCD
               x = Nombre_de_Lignes - (Ligne2 - Derligne)     'nombre de lignes entre les 2 TCDs
               .Cells(Derligne, 1).Resize(Ligne2 - Ligne1).EntireRow.Hidden = False     'montrer toutes les lignes entre les 2 TCDs
               With .Cells(Derligne, 1)      'à partir de la ligne suivante après le 1er TCD
                    If x > 0 Then .Resize(x).EntireRow.Insert     'ajouter autant de lignes pour avoir au minimum "Nombre_de_Lignes" lignes
                    .Resize(Nombre_de_Lignes - 4).EntireRow.Hidden = True     'les 4 1eres lignes avant le TCD suivant visible, tout le reste caché
               End With
          Next
     End With

End Sub

Au début, on peut ajouter "Thisworkbook.refreshall" pour renouveller tous les TCD éventuellement ...

Bonsoir BsAlv,

Avec un peu de retard, je souhaite te remercier pour ta proposition qui fonctionne super bien

Il ne me reste plus qu'à adapter ton code à mon bilan

Merci encore pour ton aide précieuse

Bonne soirée

Petitejiji

merci pour les compléments,

mais quand je relis la macro, il y a peut-être un petit modif à faire

actuel: (la 4ème dernière ligne)

.Resize(Nombre_de_Lignes - 4).EntireRow.Hidden = True 'les 4 1eres lignes avant le TCD suivant

modifier en

.Resize(Ligne2-DerLigne - 4).EntireRow.Hidden = True 'les 4 1eres lignes avant le TCD suivant

Normallement, il y a 50 (=Nombre_de_lignes, à modifier eventuellement) entre chaque TCD avec cette macro, mais le moment au le 1er TCD est devenu plus petit après renouvellement, on peut y avoir par exemple 55 lignes. La macro ne supprime pas ces 5 lignes supplémentaires, mais elle ne les cache pas non plus