Macro création automatique d'un tableau grâce aux données d'autres feuilles

[Marine64110]

Bonjour à tous !

Je vous explique mon cas : j'ai un fichier Excel avec plusieurs feuilles (correspondant chacune à un type de travaux de bâtiment), avec dans chaque feuille un montant de kWh et un prix.

Je voudrais créer un onglet de "synthèse" avec un tableau qui récapitule :

• 
  le nom de l'onglet
  le montant de kWh
  le prix

Jusque là ça paraît tout simple. C'est ici que ça se corse : je ne veux pas avoir TOUTES les feuilles récapitulées dans ce tableau, mais seulement celles qui ont un montant de kWh différent de 0... Des idées d'un code VBA qui fasse ça ?

Mon fichier d'origine étant très lourd (65 feuilles au total, avec de nombreuses macros déjà), je vous ai fait un petit fichier très simplifié avec trois feuilles "travaux" (isolation, menuiseries et éclairage) et la feuille de synthèse qui comprend le tableau que je voudrais pouvoir remplir avec les infos des 3 autres feuilles.

J'ai essayé de simplifier au maximum le problème, mais n'hésitez pas à me demander plus d'infos si ce n'est pas clair !

Merci beaucoup

 

[Modeste]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Re-bonjour Marine

Avec quelque chose comme ce qui suit dans la fenêtre de code de la feuille "calcul bouquet travaux" ... si j'ai bien compris

Private Sub Worksheet_Activate()
Me.Cells(15, 5).Resize(Application.CountA(Me.[E:E]), 3).ClearContents
For Each sh In Sheets
    If sh.Name <> Me.Name And sh.[D8] > 0 Then
        lig = Me.Cells(Rows.Count, 5).End(xlUp).Row + 1
        Me.Cells(lig, 5) = sh.Name
        Me.Cells(lig, 6) = sh.[D8]
        Me.Cells(lig, 7) = sh.[D9]
    End If
Next sh
End Sub

 

[Marine64110]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Bonjour Modeste !
Et merci de ton aide, tu as été très réactif sur tous mes petits soucis d'hier :-D

Ton code marche au poil sur le petit fichier simplifié. Par contre j'ai un peu de mal à le comprendre, du coup c'est difficile de l'adapter à mon fichier d'origine :-/
C'est surtout la ligne

Me.Cells(15, 5).Resize(Application.CountA(Me.[E:E]), 3).ClearContents

que je ne comprends pas, tu pourrais expliquer très vite fait ce qu'elle signifie stp ?

Aussi, je crois comprendre qu'une des conditions pour que ton code marche, c'est que mes résultats en kWh soient tous dans la case D8 de leur feuille respective. Or, dans mon fichier réel, ce n'est pas le cas... Comment adapter le code ?

Je suis toute débutante en VBA, je galère un peu désolée ^^


EDIT : J'ai commencé à adapter ton code à mon fichier réel, j'avance petit à petit. Tout bien réfléchi, je ne comprends pas non plus cette partie :

If sh.Name <> Me.Name And sh.[D8] > 0 Then
        lig = Me.Cells(Rows.Count, 5).End(xlUp).Row + 1

 

[Modeste]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Bonjour Marine, le forum,

tu pourrais expliquer très vite fait ce qu'elle signifie stp ?

Oh oui
elle efface les données de la synthèse précédente (mais pourquoi ai-je le sentiment que tu vas me dire cette fois que c'est un peu trop vite fait?

En "moins court": j'ai utlisé l'événement Activate de la feuille "Calcul bouquet de travaux", de sorte que, quand tu modifieras des données dans une des autres feuilles, ou même que tu ajouteras/supprimeras une/des feuille(s), la mise à jour se feras dès que tu activeras à nouveau cette feuille de synthèse.
Il est donc nécessaire d'effacer les anciennes données (si le nombre de feuilles était constant, de même que le nombre de feuilles où les kWh sont non-nuls, on pourrait se contenter "d'écraser"; ici, par prudence, je choisis d'effacer le contenu, avant de ré-insérer les données actualisées)

Me représente la feuille dans laquelle on a inséré le code
Cells(15, 5) est la cellule E15 (15e ligne, 5e colonne)
Resize permet de redimensionner une plage (d'un certain nombre de lignes et de colonnes)
Application.CountA est l'équivalent de la fonction de feuille de calcul NBVAL​

Cette ligne me permet donc d'effacer le contenu d'une plage de cellules qui démarre en E15 de la feuille "Calcul bouquet de travaux", chaque fois que cette feuille est activée, en "étendant" la zone sur autant de lignes qu'il y a de valeurs dans toute la colonne E et sur 3 colonnes.

Aussi, je crois comprendre qu'une des conditions pour que ton code marche, c'est que mes résultats en kWh soient tous dans la case D8 de leur feuille respective. Or, dans mon fichier réel, ce n'est pas le cas... Comment adapter le code ?

Ah ça, par contre, c'est ballot !!
Ma première réponse sera: il serait infiniment plus simple que ce soit le cas!
Si ce n'est pas possible, on peut s'y prendre autrement, mais

1. ça va inévitablement "alourdir" le code
2. il faut que tu nous dises comment on retrouve l'emplacement de cette mention dans les différentes feuilles, si cet emplacement n'est pas identique.

Dans l'autre portion de code qui te pose problème, on fait une boucle (For) sur toutes les feuilles du classeur. On vérifie donc que le nom de chacune de ces feuilles (sh.Name) est différent de celui de la feuille où se fera la synthèse (Me.Name) ET que la cellule D8 de chaque feuille contienne une valeur supérieure à 0 on en revient donc à la question précédente!.
Si les 2 conditions sont réunies, on repère le n° de ligne de la dernière cellule non-vide qui sera trouvée en colonne E, si on part de la dernière ligne de la feuille et qu'on "remonte" dans la colonne E. Le '+1' permet de pointer sur le n° de ligne de la première cellule vide sous les données en colonne E (il devra donc toujours y avoir un titre en E14!)

Est-ce plus clair?

 

[Marine64110]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Re bonjour Modeste !

Tout d'abord, merci beaucoup pour le temps que tu as dû passer à expliquer tout ça par écrit, c'est super gentil
Oui c'est plus clair, mais c'est encore bien compliqué pour moi...

Pour ce qui est du montant en kWh qui n'était pas dans la même cellule pour toutes les feuilles, j'ai réglé le problème : c'est maintenant la cellule A1 qui est concernée pour TOUTES les feuilles

J'ai essayé d'adapter un peu ton code à mon cas précis, notamment en mettant A1 et A2 au lieu de D8 et D9 et en changeant la cellule du tableau. Mais ça ne fonctionne pas
D'autre part, j'ai dans mon fichier deux feuilles qui ne doivent pas apparaître dans le tableau récapitulatif : la feuille Sommaire et la feuille Catégories. Comment les exclure de la macro ?

Je te poste mon fichier réel (j'ai mis quasi toutes mes autres macros en commentaires pour ne pas tout polluer) ici : Ce lien n'existe plus
La feuille Calcul bouquet de travaux est la feuille 37 dans mon code VBA. Les feuilles travaux sont tous les onglets qui sont en couleurs.

J'ai également une autre petite question (un truc de base qui n'a pas grand chose à voir avec le reste, c'est plus pour ma culture gé' ) : si, dans une macro, je fais référence à une cellule (par exemple C8) et que dans la feuille concernée j'insère une colonne avant la colonne C (ma cellule C8 devient donc D8), est-ce que ce sera pris en compte dans ma macro ?

Merci beaucoup

 

[Modeste]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Bonsoir,

Ah vraiment, on n'avait nul besoin d'avoir à disposition ton fichier complet Il suffisait (et encore!) d'ajouter à ton exemple de départ, 2 feuilles à ne pas prendre en considération et de copier tes données en A1 comme tu l'as fait!

Les modifications que tu as faites pour "exclure" les 3 feuilles sont tout à fait correctes. Tester le contenu de A1 c'est bon aussi. La seule chose qu'il restait à modifier étaient les numéros des colonnes quand tu recopies les valeurs des différentes feuilles ... exactement comme tu l'as fait au début du code: le nom de la feuille ne doit plus apparaître en colonne E mais en colonne C.
Donc

Me.Cells(lig, 5) = Sh.Name

... devient

Me.Cells(lig, 3) = Sh.Name

Même type de modif aux deux lignes suivantes dans le code et ça devrait être bon!?


Pour ce qui est de ta question de culture gé' ... je ne sais pas trop ce que tu veux savoir? Si tu espères secrètement que, en ayant écrit dans le code (comme je l'ai fait) Sh.[A1] ou Me.Cells(1, 1), le code sera automatiquement transformé en Sh.[B1] ou Me.Cells(1, 2) juste parce que tu as ajouté une nouvelle colonne devant la A ... eh bien, tu oublies (ce genre de choses fonctionne avec les formules; pas avec le code)

Enfin, si tu nous avais dit que le "repère" pour la position des kWh était que le texte de la cellule voisine serait toujours "TOTAL (kWh cumac)", on aurait pu le chercher en colonne B (mais ce que tu as fait fonctionnera aussi ... surtout que tu as parfois 2 tableaux sur une feuille!)

 

[Marine64110]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Bonjour Modeste,

Je me sens bête... J'avais testé TOUTES les modifications que tu as écrites là, mais je n'ai probablement pas testé tout en même temps. Il y avait donc toujours quelque chose qui clochait

En tous cas merci beaucoup ! Et pour la petite question subsidiaire, je me doutais un peu de cette réponse (même si j'espérais très très très fort que ce ne soit pas le cas )

Ca marche nickel maintenant, je vais juste faire deux trois petites modif' pour que ce soit plus joli et compréhensible par l'utilisateur de mon fichier.

J'ai une dernière requête (après je ne t'embête plus, promis !) : j'avais fait un tableau de 5 lignes de façon arbitraire. Or, il se peut très bien que l'utilisateur du fichier veuille remplir seulement 2 feuilles (donc 2 lignes du tableau), ou bien 15. Comment faire pour que la taille du tableau s'adapte automatiquement (avec les bordures et tout et tout) ?

Merci encore

 

[Modeste]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Salut Marine,

après je ne t'embête plus, promis !

On dirait une de ces résolutions prises le jour du Nouvel-An et qui tiennent jusqu'au ... 2 janvier
Et puis, si plus personne ne posait de question, qu'est-ce qu'on s'ennuierait!

On peut dessiner les bordures dans le code, mais on peut aussi se contenter d'une Mise en Forme Conditionnelle appliquée à tes trois colonnes sur ... autant de lignes que tu pourrais avoir de feuilles concernées au maximum (20, 50, ou 100, peu importe).

Sélectionne donc cette plage > MFC > Nouvelle règle > "Appliquer une mise en forme uniquement aux cellules qui contiennent" > dans la première liste en bas à gauche, sélectionne "Aucune cellule vide" > Format > Applique les bordures de ton choix ... et Valide.
Exécute ensuite la macro avec un nombre de feuilles différent, pour vérifier que ça fonctionne comme tu le souhaites ... et par pitié, reviens poser des questions, encore et encore

 

[Marine64110]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Ah chouette, ça c'est une astuce bien pratique et que je ne connaissais pas ! Ca marche super bien, merci.

et par pitié, reviens poser des questions, encore et encore

C'est si gentiment demandé Du coup j'ai encore une dernière question héhé ! La taille de mon tableau s'adapte maintenant toute seule. Mais j'aurais aimé avoir une ligne "Total" en-dessous de la dernière ligne, qui fasse la somme de ce qu'il y a au-dessus. Comment faire pour que cette ligne "suive" les autres (ie qu'elle soit toujours placée juste en-dessous de la dernière ligne du tableau) ?
Je mets une petite image pour illustrer ce que je veux (j'ai toujours l'impression de ne pas être très claire)

Merci

 

[Modeste]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Du coup j'ai encore une dernière question héhé !

Tout bien réfléchi, ... vous avez dépassé votre quota!

Avec les explications que j'avais données précédemment, tu verras bien si tu arrives à t'y retrouver (si pas, tu reviendras poser une dernière question )

Cette instruction

Me.Cells(lig + 2, 4) = Application.Sum(Me.[D15].Resize(lig - 14, 1))

placée juste après le Next sh, affichera la somme des valeurs en colonne D (de la ligne 15 à la dernière cellule utilisée dans cette colonne).

Si tout va bien, tu devrais pouvoir faire la même chose en colonne E (je n'ai pas ouvert ton "monstre" sur Cjoint; tu adapteras les colonnes si je me suis trompé)
Si pas, tu sais où nous trouver

 

[Marine64110]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Hum, j'ai une erreur d'exécution...

Pourtant j'ai bien fait comme tu as dit, placé l'instruction après Next Sh :

Private Sub Worksheet_Activate()
Me.Cells(13, 3).Resize(Application.CountA(Me.[C:C]), 3).ClearContents
For Each Sh In Sheets
    If Sh.Name <> Me.Name And Sh.Name <> Feuil1.Name And Sh.Name <> Feuil2.Name And Sh.[A1] > 0 Then
        lig = Me.Cells(Rows.Count, 3).End(xlUp).Row + 1
        Me.Cells(lig, 3) = Sh.[A3]
        Me.Cells(lig, 4) = Sh.[A1]
        Me.Cells(lig, 5) = Sh.[A2]
    End If
Next Sh
Me.Cells(lig + 2, 4) = Application.Sum(Me.[D15].Resize(lig - 63, 1))
Me.Cells(lig + 2, 5) = Application.Sum(Me.[E15].Resize(lig - 63, 1))
End Sub

J'ai adapté ton code à mon cas (j'ai mis 63 car j'ai 63 feuilles au total, donc possiblement 63 lignes dans mon tableau, dans le pire des cas. C'était bien là qu'il fallait mettre ça )

Tout bien réfléchi, ... vous avez dépassé votre quota!

Déjà ??

 

[Modeste]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Re,

Quand j'écris Resize(lig - 14, 1), c'est parce qu'à la fin de la boucle For, lig a comme valeur le dernier n° de ligne complété de la feuille.

Imaginons que tu n'aies que 3 feuilles à traiter: lig vaudrait donc 17, à la sortie du For. Si tu demandes à Excel de faire 17 - ... 63 il est assez normal qu'il nous fasse un petit malaise!
Le 14 n'est nullement lié au nombre de feuilles, mais c'est le nombre à soustraire à la variable lig pour déterminer le nombre de cellules à additionner (et 17-14 donnera bien 3). Tu dois donc laisser 14, tant que le premier nombre sera inscrit en ligne 15.

Teste comme ça et on verra si c'est mieux!

 

[Marine64110]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Re,

Pourtant avec 14, ça ne marche pas non plus...

J'ai fait des tests :

- si j'ai 0 ou 1 feuille de remplie dans mon fichier (donc 0 ou 1 ligne dans mon tableau), ton code fonctionne jusqu'à Resize(lig - 12, 1). Mais ma ligne Total est fausse : A partir de 13, j'ai une "erreur 1004".

- si j'ai 2 feuilles remplies (donc 2 lignes dans mon tableau), je peux écrire Resize(lig - 13, 1) mais ça bug toujours avec 14 et ma ligne Total est toujours fausse.

- si j'ai 3 feuilles ou plus, là ça fonctionne avec Resize(lig - 14, 1) mais dans la ligne Total j'ai uniquement la somme des lignes à partir de la ligne 15 :

Du coup, j'en ai conclu que, dans l'instruction que tu m'avais donnée, il ne faut pas mettre [D15] et [E15] mais D[13] et E[13].

J'ai modifié, Excel me fait maintenant la bonne somme. Par contre, il semblerait qu'il n'apprécie toujours pas le Resize(lig - 14, 1) quand j'ai moins de 3 lignes dans mon tableau...

(Tu l'auras remarqué, je ne comprends pas grand chse à ce que je fais. Je fais des tests, je bidouille )


EDIT : Finalement, ça ne marche toujours pas pour la somme après ma modif. Il semblerait que la macro ne me fasse la somme que de 2 lignes seulement... Ca se complique, je ne vois pas la solution

 

[Si...]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

salut

autre proposition quand on peut utiliser l'outil Tableau.
J'ai retiré des onglets pour alléger l'envoi mais à voir sur place.

 

[Marine64110]

Re : Macro création automatique d'un tableau grâce aux données d'autres feuilles

Salut

La somme fonctionne, mais ce n'est pas tout à fait ce que je veux... En effet, je veux que lorsque la case "montant kWh" est nulle dans une feuille, cette ligne n'apparaisse pas dans le tableau.

Je crois qu'on est tout près de la solution avec ce que Modeste m'a donné, il ne manque pas grand chose pour que tout fonctionne

Mais merci beaucoup d'avoir pris le temps de te pencher sur mon problème