Consolider plusieurs onglet avec un nb de ligne différent

Le 28/09/2017 à 11:43

Bonjour,

après avoir fait plusieur test je me permet de venir solliciter votre aide.

Je cherche à consolider plusieurs onglet qui n'ont pas le même nombre de ligne.

Je sais que je dois détecter la dernière ligne non vide afin de copier mes valeurs à la suite cependant si j'arrive à selectionner l'intégralité de ma plage peux importe ça taille, je n'arrive pas à faire que la copie arrive au bon endroit.

Aujourd'hui j'ai ce code :

Sub Macro3()
    Sheets("C3511").Select
    Dim DerLigne As Long
    DerLigne = Sheets("C3511").Cells(65536, 1).End(xlUp).Row
    Sheets("C3511").Range("A31:AX" & DerLigne).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Consolidé").Select
    Dim LastRw As Long
    LastRw = Sheets("Consolidé").Range("A65536").End(xlUp).Row
    ActiveSheet.Paste
End Sub

Je vous joins mon fichier d'exemple.

Je ne dois rien toucher au ligne 1 à 29 et faire mon copier coller à partir de la ligne 30 pour le premier onglet.

Dans l'exemple je n'ai que 2 onglet mais j'en aurais en réalité 21.

Aujourd'hui ma macro colle la ou j'ai selectionner et non a partir de la dernière ligne.

Je vous remercie par avance pour votre aide

19consolidation-onglet.xlsm (293.63 Ko)
Le 28/09/2017 à 15:09

Bonjour (... on ne se bouscule pas au portillon, semble-t-il!?)

Dans tes deux feuilles, tu n'as pas exactement la même présentation: en "C3511", la ligne 30 est masquée et contient des titres de colonnes. Dans la seconde feuille, des données sont insérées à partir de la ligne 30.

Tu utilises des Tableaux dans tes différentes feuilles, mais le code dont tu t'es inspiré (je suppose) n'en tient pas compte. Le code suivant devrait être opérationnel si les données (ligne 30 et suivantes) de la feuille Consolidé sont effacées et que le tableau actuel pour la même zone est re-converti en plage!

Tout n'est pas finalisé, ni optimisé: c'est juste un point de départ qu'il faudra améliorer ... commence par vérifier et corriger les points relevés ci-dessus, teste la proposition, détermine si tu utilises (ou pas) un Tableau dans la feuille Conso, etc.

Sub Macro3()

Dim DerLigne As Long, LastRw As Long
Dim f As Worksheet
For Each f In Sheets 'on boucle sur toutes les feuilles du classeur
    If f.Name <> "Consolidé" And f.Name <> "NE PAS SUPPRIMER Données TM1" Then
        DerLigne = f.Cells(65536, 1).End(xlUp).Row 'dernière ligne de chaque feuille passée en revue
        With Sheets("Consolidé")
            LastRw = .Range("A65536").End(xlUp).Row + 1 'première ligne vide de la feuille conso
            f.Range("A31:AX" & DerLigne).Copy
            .Cells(LastRw, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'correction
        End With
    End If
    Application.CutCopyMode = False
Next f
End Sub

[Edit:] j'ai vu (avec du retard) que tes feuilles sources contenaient des formules ... Un simple copier-coller donne donc des résultats erronés. J'ai corrigé une ligne du code ci-dessus, pour faire un "collage spécial > valeurs et formats des nombres"

Le 28/09/2017 à 17:21

Bonjour,

Merci pour ta solution, cependant, le format tableau m'est indispensable car ce fichier est partagé et rempli par des personnes qui ne maitrise pas excel et elle peuvent être amener a ajouter des lignes et c'est aujourd'hui la seule façon que j'ai trouver pour assurer que les formules se recopie et qu'elle ne casse pas l'intégralité des formules...

Pour la différence entre les deux onglet c'est une erreur du à un test de ma part j'ai remis les onglets ISO.

Ta macro marche très bien, cependant puis-je lui dire de ne le faire que sur certain onglet que je nommerais ? en effet, je dois faire un regroupement des 21 onglet, mais également 6 regroupement selon des pôle définie ?

J'ai également une question, je vais être amener a répéter souvent cette opération de consolidation, je voulais donc clear les données avant de venir coller les onglets, cependant, j'ai du faire une erreur car je me trouve avec de nombreuse cellule vide en complément de celle que je viens coller. De plus l'idéal pour moi ne serais pas de préciser la plage de clear mais de lui dire de clear l'ensemble des cellules non vide.

J'ai fait ça :

Sub Macro3()
Worksheets("Consolidé").Range("A31:AX2000").Select
Worksheets("Consolidé").Range("A31:AX2000").Clear
Dim DerLigne As Long, LastRw As Long
Dim f As Worksheet
For Each f In Sheets 'on boucle sur toutes les feuilles du classeur
   If f.Name <> "Consolidé" And f.Name <> "NE PAS SUPPRIMER Données TM1" Then
        DerLigne = f.Cells(65536, 1).End(xlUp).Row 'dernière ligne de chaque feuille passée en revue
       With Sheets("Consolidé")
            LastRw = .Range("A65536").End(xlUp).Row + 1 'première ligne vide de la feuille conso
           f.Range("A31:AX" & DerLigne).Copy
            .Cells(LastRw, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'correction
       End With
    End If
    Application.CutCopyMode = False
Next f
End Sub

Quel est le problème dans le code ?

En te remerciant encore pour ta réponse,

Le 28/09/2017 à 21:49

Bonsoir,

Les tableaux dans les différentes feuilles sources ne seront pas un problème. C'est dans la feuille "Consolidé" qu'il faut que tu décides si tu dois ou veux utiliser un tableau.

Yuukia a écrit :

je voulais donc clear les données avant de venir coller les onglets, cependant, j'ai du faire une erreur car je me trouve avec de nombreuse cellule vide en complément de celle que je viens coller

À mon avis (ça n'engage que moi) je dirais que le problème est lié à ton tableau, justement: le fait d'effacer une plage de cellules ne redimensionne pas le tableau (qui existe donc toujours, avec sa taille d'origine).

Donc si tu utilises un tableau dans cette feuille Conso, tu devrais utiliser une syntaxe propre aux ListObjects ... ou ne pas utiliser de tableau dans cette feuille-là ... c'est à toi de voir!

Pour travailler sur certains onglets (mais c'est déjà ce que fait ma proposition: elle ignore deux des feuilles du classeur!?), tu peux aussi créer un tableau en mémoire (Array) , contenant les noms des feuilles à explorer. Ça donnerait quelque chose du genre:

tabloFeuilles = Array("C3511", "G5011")
For i = 0 To UBound(tabloFeuilles)
    DerLigne = ThisWorkbook.Sheets(tabloFeuilles(i)).Cells(Rows.Count, 1).End(xlUp).Row
    'etc
Next i
Le 28/09/2017 à 22:49

Bonjour

Le maniement des tableaux par VBA doit utiliser les possibilités des tableaux

derligne est inutile tout comme f.Range("A31:AX" & DerLigne).Copy : il faut utiliser les propriétés des Listobjects.

Cependant il y a plusieurs problèmes dans ton fichier :

  • sur l'onglet C3511 pourquoi une ligne de titre colonne1 à colonne x alors que les deux autres tableaux utilisent bien les en-têtes de la ligne 29 ?
  • des tableaux non nommés : ne jamais garder les noms automatiques surtout si on gère par VBA
  • des formules RECHERCHEV propres à chaque tableau : il faut soit copier le résultat et non les formules soit reparamétrer les formules dans le tableau consolidé

Si on ne recopie pas les formules ce code fait le job :

    With Worksheets("Consolidé").ListObjects(1)
        For Each WS In ThisWorkbook.Worksheets
            If WS.Name <> "Consolidé" And WS.Name <> "NE PAS SUPPRIMER Données TM1" Then
                x = .ListRows.Count + 1 + .Range.Cells(1, 1).Row
                WS.ListObjects(1).DataBodyRange.Copy
                Worksheets("Consolidé").Cells(x, .ListColumns(1).Range.Column).PasteSpecial xlPasteValues
            End If
        Next WS
    End With

Néanmoins des tests peuvent être nécessaires pour vérifier que les tableaux source ne sont pas vides, et peut-être faudrait il vider au préalable le tableau cible...

Le 29/09/2017 à 09:09

Bonjour (tous)

Pour résumer... toutes les questions, toutes les réponses et même les commentaires !

Pour pouvoir réaliser une application qui "tient la route" il faut

  1. lâcher le clavier et la souris
  2. ne plus regarder l'écran
  3. prendre un bloc-notes, un crayon et une gomme
  4. faire un 1er dessin de "ce que l'on a" - les données à disposition
  5. faire un 2ème dessin de "ce que l'on veut" - les données à présenter/calculer
  6. faire un 3ème dessin de "comment on veut présenter les résultats"
  7. faire un dernier dessin qui fait la synthèse des 3 premiers
  8. au cas où l'on arrive pas à faire le 4ème dessin, il y a plusieurs possibilités
      refaire les 3 premiers dessins
    • faire une pause
    • oublier le papier-crayon
    • penser informatique
    • demander conseil
  • dès que le 4ème dessin est réalisé il y a plusieurs possibilités également
      le résultat convient
      reprendre chaque dessin du 1er au 4ème pour vérifier que l'on a rien oublié (cas particulier, données complémentaires...)
    1. faire une pause, oublier le papier-crayon, penser informatique
    2. se lancer mais pas tête baissée
  • le résultat ne convient pas tout à fait
      reprendre chaque dessin du 1er au 4ème parce que l'on a forcément oublié quelque chose (cas particulier, données complémentaires...)
    1. faire une pause, oublier le papier-crayon, penser informatique, ne pas se lancer tête baissée
    2. demander conseil avant de s'enliser
  • penser à faire des tableaux précis
      qui ne présentent qu'une seule catégorie d'information
    1. qui comporte un minimum de colonnes
  • utiliser les possibilités d'Excel
      éviter de recopier plusieurs fois les mêmes choses (en VBA, en Formules...)
    1. utiliser des "fonctions nommées" pour alléger les formules complexes
  • penser au confort d'utilisation (pour l'utilisateur et pour celui qui conçoit)
  • penser que l'application devra forcément évoluer
  • penser que les utilisateurs trouveront toujours "un quelque chose" à ajouter u à redire
  • prévoir ces évolutions
  • demander conseil avant de s'enliser
  • etc...

    • En plus "bref" chaque métier a ses spécificités, ses méthodes, ses outils... Excel est un très bon outil mais il y a surtout des gens qui savent s'en servir parce qu'il pensent informatique

    Le 29/09/2017 à 11:59
    78chris a écrit :

    Bonjour

    Le maniement des tableaux par VBA doit utiliser les possibilités des tableaux

    derligne est inutile tout comme f.Range("A31:AX" & DerLigne).Copy : il faut utiliser les propriétés des Listobjects.

    Cependant il y a plusieurs problèmes dans ton fichier :

    • sur l'onglet C3511 pourquoi une ligne de titre colonne1 à colonne x alors que les deux autres tableaux utilisent bien les en-têtes de la ligne 29 ?
    • des tableaux non nommés : ne jamais garder les noms automatiques surtout si on gère par VBA
    • des formules RECHERCHEV propres à chaque tableau : il faut soit copier le résultat et non les formules soit reparamétrer les formules dans le tableau consolidé

    Si on ne recopie pas les formules ce code fait le job :

        With Worksheets("Consolidé").ListObjects(1)
        For Each WS In ThisWorkbook.Worksheets
            If WS.Name <> "Consolidé" And WS.Name <> "NE PAS SUPPRIMER Données TM1" Then
                x = .ListRows.Count + 1 + .Range.Cells(1, 1).Row
                WS.ListObjects(1).DataBodyRange.Copy
                Worksheets("Consolidé").Cells(x, .ListColumns(1).Range.Column).PasteSpecial xlPasteValues
            End If
        Next WS
    End With

    Néanmoins des tests peuvent être nécessaires pour vérifier que les tableaux source ne sont pas vides, et peut-être faudrait il vider au préalable le tableau cible...

    Edit : correction de la mise en forme de mon post qui n'était pas OK

    Rechercher des sujets similaires à "consolider onglet ligne different"