Sub CopierCollerTab()
Dim ShtS As Worksheet, ShtD As Worksheet
Dim Col1 As Long, ColFin As Long
Dim LigFin As Long
' Définir les feuilles Source et celle de Destination
Set ShtS = Worksheets("départ")
Set ShtD = Worksheets("arrivée")
' Initialiser les variables
Col1 = 1
' Avec la feuille de destination
With ShtD
' Calculer la dernière ligne et colonne
ColFin = .Cells(1, Columns.Count).End(xlToLeft).Column
LigFin = .Cells(Rows.Count, 1).End(xlUp).Row
' Effacer le contenu des cellules
.Range(.Cells(1, 1), .Cells(LigFin, ColFin)).ClearContents
End With
' Copier le 1er tableau
ColFin = ShtS.Cells(1, Col1).End(xlToRight).Column
LigFin = ShtS.Cells(Rows.Count, Col1).End(xlUp).Row
ShtS.Range(ShtS.Cells(1, Col1), ShtS.Cells(LigFin, ColFin)).Copy Destination:=ShtD.Range("A1")
' Boucler pour chaque tableau
Do While ColFin < Columns.Count
' 1ère colonne du prochain tableau
Col1 = ShtS.Cells(1, ColFin).End(xlToRight).Column
' Colonne de fin
ColFin = ShtS.Cells(1, Col1).End(xlToRight).Column
' Ligne de fin
LigFin = ShtS.Cells(Rows.Count, Col1).End(xlUp).Row
If ColFin < Columns.Count Then
' Copier coller le tableau sans l'entête
ShtS.Range(ShtS.Cells(2, Col1), ShtS.Cells(LigFin, ColFin)).Copy Destination:=ShtD.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Loop
' Libérer les variables objet
Set ShtD = Nothing
Set ShtS = Nothing
End Sub