Sub Consolidation() Dim ShtS As Worksheet, DLigS As Long, DLigD As Long
Dim ShtD As Worksheet ' Feuille de destination
' Définir la feuille de destination
Set ShtD = Sheets("MaFeuille")
' Pour chaque feuille
For Each ShtS In ActiveWorkbook.Sheets
If ShtS.Name <> ShtD.Name Then
' Dernière ligne remplie de la feuille de destination
DLigD = ShtD.Range("A" & Rows.Count).End(xlUp).Row
' Dernière ligne remplie de la feuille source
DLigS = ShtS.Range("A" & Rows.Count).End(xlUp).Row
' copier les données de la colonne A à C par exemple
ShtS.Range("A2:C" & DLigS).Copy
ShtD.Range("A" & DLigD).PasteSpecial Paste:=xlPasteValues
End If
Next ShtS
End Sub
Sub Consolidation()
Dim ShtS As Worksheet, DCol As Integer, DLigS As Long, DLigD As Long
Dim ShtD As Worksheet ' Feuille de destination
' Définir la feuille de destination
Set ShtD = Sheets("Consolidation")
' Pour chaque feuille
For Each ShtS In ActiveWorkbook.Sheets
If ShtS.Name <> ShtD.Name Then
' Dernière ligne remplie de la feuille de destination
DLigD = ShtD.Range("A" & Rows.Count).End(xlUp).Row
' Dernière colonne à copier
DCol = ShtS.Cells(1, Columns.Count).End(xlToLeft).Column
' Dernière ligne remplie de la feuille source
DLigS = ShtS.Range("A" & Rows.Count).End(xlUp).Row
' copier les données de la colonne A à C par exemple
ShtS.Cells.UnMerge
ShtS.Range(ShtS.Cells(1, 1), ShtS.Cells(DLigS, DCol)).Cut
ShtD.Range("A" & DLigD + 1).Select
ShtD.Paste
End If
Next ShtS
End Sub
Option Explciit
Sub ConcatenationFeuilles()
Dim T() As Variant
Dim Ws As Worksheet
Application.ScreenUpdating = False
ShConcat.Cells.Clear
For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> ShConcat.Name Then
T = Ws.UsedRange
ShConcat.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(T, 1), UBound(T, 2)) = T
End If
Next Ws
Erase T
Application.ScreenUpdating = True
End Sub