Consolider plusieurs onglet avec un nb de ligne différent
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
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"
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,
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.
À 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).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
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
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...
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
- lâcher le clavier et la souris
- ne plus regarder l'écran
- prendre un bloc-notes, un crayon et une gomme
- faire un 1er dessin de "ce que l'on a" - les données à disposition
- faire un 2ème dessin de "ce que l'on veut" - les données à présenter/calculer
- faire un 3ème dessin de "comment on veut présenter les résultats"
- faire un dernier dessin qui fait la synthèse des 3 premiers
- 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
- 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...)
- reprendre chaque dessin du 1er au 4ème parce que l'on a forcément oublié quelque chose (cas particulier, données complémentaires...)
- qui ne présentent qu'une seule catégorie d'information
- éviter de recopier plusieurs fois les mêmes choses (en VBA, en Formules...)
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
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