Concaténer des feuilles xls dans une seule feuille

[MJ13]

Bonjour à tous

Voila, j'ai un classeur avec 167 feuilles (remplies sur une seule colonne) qui ont une structure comme nom Ntxt avec N pour le numéro de feuille avec pour chaque feuille de 1 à 60 données.

Comment en VBA avec une macro sympa puis-je concaténer les 167 feuilles les unes à la suite des autres dans une feuille que je rajouterai au début de la macro et qui s'appelera Données?

Merci d'avance.

 

[GeoTrouvePas]

Re : Concaténer des feuilles xls dans une seule feuille

Bonjour MJ,

Deux ou trois questions avant de commencer à me pencher sur le sujet :

- Est ce que c'est tjs la même colonne qui est remplie dans les feuilles Ntxt ?
- "1 à 60 données par feuilles" : Toutes à la suite les unes des autres dans la même colonne ?
- "concaténer les 167 feuilles" : Tu veux les mettre bout à bout dans la même colonne ou copier 1Txt dans la colonne 1 de "Données", 2Txt dans la colonne2....... ?

 

[Pierrot93]

Re : Concaténer des feuilles xls dans une seule feuille

Bonjour Michel🙂, Geo

regarde peut être le code ci-dessous, si j'ai bien compris :

Option Explicit
Sub test()
Dim i As Byte
Application.ScreenUpdating = False
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Données"
For i = 1 To Sheets.Count - 1
    With Sheets(i)
        .Range("A1").Resize(.Range("A65536").End(xlUp).Row, 1).Copy _
            Sheets("Données").Range("A65536").End(xlUp)(2)
    End With
Next i
Application.ScreenUpdating = True
End Sub

bonne journée
@+

 

[MJ13]

Re : Concaténer des feuilles xls dans une seule feuille

Bonjour Geo

- Est ce que c'est tjs la même colonne qui est remplie dans les feuilles Ntxt ?
- "1 à 60 données par feuilles" : Toutes à la suite les unes des autres dans la même colonne ?
- "concaténer les 167 feuilles" : Tu veux les mettre bout à bout dans la même colonne ou copier 1Txt dans la colonne 1 de "Données", 2Txt dans la colonne2....... ?

En fait je n'ai qu'une seule colonne pour chaque feuille et je veux mettre dans une feuille sur une colonne les feuilles 1TXT puis en dessous en col1 2TXT jusque 167 txt.

Merci si cela te dit.

Edit: bonjour Pierrot 🙂. Je teste et je vous dis.

 

[GeoTrouvePas]

Re : Concaténer des feuilles xls dans une seule feuille

Dans l'hypothèse où :

- Les données contenues dans les feuilles Ntxt sont placées dans la colonne 1 et contigües
- Tu veux placer ces données "bout à bout" dans la colonne 1 de la feuille "Données"

voilà une procédure adaptée :

Sub ConcatenerMesFeuilles

Sheets.Add
With ActiveSheet
    .Name = "Données"
    .Move Before:=Sheets(1)
End With

j = 1

For NFeuille = 2 To Sheets.Count

    i = 1
 
    While Sheets(NFeuille).Cells(i, 1) <> ""
        Sheets("Données").Cells(j, 1) = Sheets(NFeuille).Cells(i, 1)
        i = i + 1
        j = j + 1
    Wend

Next

End Sub

moi qui aide un barbatruc........ j'y crois pas 😎😎😎

 

[GeoTrouvePas]

Re : Concaténer des feuilles xls dans une seule feuille

Ah ben on s'est croisés. J'avais vu juste dans ce que tu voulais.

Bonne journée

 

[GeoTrouvePas]

Re : Concaténer des feuilles xls dans une seule feuille

Ah bonjour Pierrot 🙂

J'avais pas vu que tu m'avais grillé sur le fil 😛

2min d'écart avec un Barbatruc pour répondre et avec un code correct ....... je suis content de moi 😛

Bon certes, je me suis pas embété à faire mes déclarations, ni à utiliser copy, ni à bloquer le screenupdating. Mais je suis quand même content de moi 😀😀😀

 

[Pierrot93]

Re : Concaténer des feuilles xls dans une seule feuille

Re,

A noter qu'il est tout de même préférable de déclarer et "typer" les variables, cela évite souvent des erreurs et permet plus facilement la détection de celles-ci le cas échéant....

 

[GeoTrouvePas]

Re : Concaténer des feuilles xls dans une seule feuille

Je suis tout à fait conscient de l'utilité des déclarations et je le fais sytématiquement sur les "grosses" macros. Mais là j'avoue que je suis allé un peu vite, que je pouvais tester facilement et sans danger l'exactitude de ma macro et que j'ai eu la flemme ^^

 

[MJ13]

Re : Concaténer des feuilles xls dans une seule feuille

Re Geo, Bonjour Pierrot (je ne t'avais pas vu car j'avais validé et quitté)

Merci à tous les 2. Vos 2 solutions fonctionnent parfaitement.

J'ai juste modifié les codes ainsi (car j'avais des feuilles de traitement entre les txt):

'Option Explicit
Sub test_concatene_Pierrot()
Dim i As Byte 'attention si Sup à plus de 255 mettre un autre type
'Stop
Application.ScreenUpdating = False
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Données"
n = 167
For i = 1 To 167 'Sheets.Count - 1
iNom = i & "txt"
    With Sheets(iNom)
        .Range("A1").Resize(.Range("A65536").End(xlUp).Row, 1).Copy _
            Sheets("Données").Range("A65536").End(xlUp)(2)
    End With
Next i
Application.ScreenUpdating = True
End Sub
Sub ConcatenerMesFeuilles_Geo()
Sheets.Add
With ActiveSheet
    .Name = "Données"
    .Move Before:=Sheets(1)
End With
j = 1
For NFeuille = 1 To 167 'Sheets.Count
iNom = NFeuille & "txt"
    i = 1
' Stop
    While Sheets(iNom).Cells(i, 1) <> ""
        Sheets("Données").Cells(j, 1) = Sheets(iNom).Cells(i, 1)
        i = i + 1
        j = j + 1
    Wend
Next
End Sub

C'est vraiment des codes super optimisés et assez rapides (j'aurais pas fait mieux 😕).

moi qui aide un barbatruc........ j'y crois pas 😎😎😎

Geo: Mais tu es peut-être un futur barbatruc qui s'ignore 🙂.

Bon Week-end 😉.

 

[GeoTrouvePas]

Re : Concaténer des feuilles xls dans une seule feuille

Mais tu es peut-être un futur barbatruc qui s'ignore 🙂.

Mais qui sais ! C'est surtout que je passe beaucoup de temps en ce moment sur VBA et XLD mais ça ne sera pas toujours comme ça. Une fois mon application terminée, je n'aurai pas autant l'occasion de passer sur le fofo.

Le code de Pierrot est bien plus optimisé et à peu près 2 fois plus rapide sur le fichier test que je me suis concocté par curiosité.

 

[Efgé]

Re : Concaténer des feuilles xls dans une seule feuille

Bonjour à tous 🙂
Très en retard mais comme je l'ai fait...

Sub MJ13()
Application.ScreenUpdating = False
Dim K As Long, i As Long
Dim Tableau()
K = 1
ReDim Tableau(1)
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Données"
For Each F In ActiveWorkbook.Worksheets
    If F.Name  "Données" And IsNumeric(Left(F.Name, 1)) Then
        For i = 1 To F.Cells(Rows.Count, "A").End(xlUp).Row
            Tableau(K) = F.Cells(i, 1)
            K = K + 1
            ReDim Preserve Tableau(K)
        Next i
    End If
Next F
For i = 1 To K
    Cells(i, 1).Value = Tableau(i)
Next i
Application.ScreenUpdating = True
End Sub

Cordialement
Edit : Balises highlight

 

[MJ13]

Re : Concaténer des feuilles xls dans une seule feuille

Re, Bonjour Efgé

Et merci aussi pour ta pierre à l'édifice. En plus c'est très bien car je ne maîtrise vraiment pas les tableaux. Et c'est ultra rapide 🙂.

Comme dit précédemment, il ya pas mal d'autres feuilles donc j'ai modifié ton code en testant sur la droite du nom du fichier.

Sub Concatène_Efgé()
Application.ScreenUpdating = False
Dim K As Long, i As Long
Dim Tableau()
K = 1
ReDim Tableau(1)
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "DonnéesEfgé"
For Each F In ActiveWorkbook.Worksheets
    If F.Name <> "Données" And Right(F.Name, 3) = "txt" Then
        For i = 1 To F.Cells(Rows.Count, "A").End(xlUp).Row
            Tableau(K) = F.Cells(i, 1)
            K = K + 1
            ReDim Preserve Tableau(K)
        Next i
    End If
Next F
For i = 1 To K
    Cells(i, 1).Value = Tableau(i)
Next i
Application.ScreenUpdating = True
End Sub

Bon Week-end 🙂.

 

[Efgé]

Re : Concaténer des feuilles xls dans une seule feuille

Re
Comme je suis en congés ce soir, je ne suis pas bousculé.
Je propose une modif à mon code qui devrait gagné du temps (suppression de la dernière boucle) .
J'ai pris en compte la modif pour les noms de feuilles.

Sub MJ13_2()
Application.ScreenUpdating = False
Dim K As Long, i As Long
Dim Tableau()
K = 1
ReDim Tableau(K)
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Données"
For Each F In ActiveWorkbook.Worksheets
    If F.Name  "Données" And Right(F.Name, 3) = "txt" Then
        For i = 1 To F.Cells(Rows.Count, "A").End(xlUp).Row
            Tableau(K) = F.Cells(i, 1)
            K = K + 1
            ReDim Preserve Tableau(K)
        Next i
    End If
Next F
Range("A1").Resize(UBound(Tableau)) = Application.Transpose(Tableau)
Application.ScreenUpdating = True
End Sub

Cordialement

Edit : Balises highlight

 

[MJ13]

Re : Concaténer des feuilles xls dans une seule feuille

Re à tous

Merci Efgé pour cette deuxième version. Si j'ai le temps de faire une prochaine version de mon utilitaire d'ici la fin de l'année, je ne manquerai pas de mettre toutes ces versions. En plus c'est rapide.

Pour 8346 cellules situées sur 167 onglets (fichier de 45 Mo de 339 onglets) voici les résultats de vitesse (bien que je n'aime pas trop les concours) mais la c'est pour notre culture personnelle (et xldienne 😛).

Pierrot: 3.4 secondes (rapide 🙂)
Geo: 11,9 secondes (un peu à la traîne, mais acceptable 😉)
Efgé1: 2.8 secondes (rapide 🙂)
Efgé2: 0.4 secondes (très rapide 🙂🙂)

Bon Week-end 🙂.