Sub Test()
Dim FeRecap As Worksheet
Dim FeCible As Worksheet
Dim Plage As Range
Dim Lig As Long
Set FeRecap = Worksheets("Feuil1")
For Each FeCible In Worksheets
If FeCible.Name <> FeRecap.Name Then
With FeCible: Set Plage = .Range(.Cells(1, 6), .Cells(.Rows.Count, 7).End(xlUp)): End With
With FeRecap
Lig = .Cells(.Rows.Count, 6).End(xlUp).Row + 1
.Range(.Cells(Lig, 6), .Cells(Lig + Plage.Rows.Count - 1, 7)).Value = Plage.Value
End With
End If
Next FeCible
End Sub