Option Base 1
Sub test()
Dim i As Integer: i = 1
Dim tabl() As Variant
ReDim tabl(i)
Dim Ws As Worksheet
For Each Ws In Worksheets
If Ws.Name Like "Equipe" & "*" Then
If UBound(tabl) = 1 Then
tabl(UBound(tabl)) = Ws.Range(Ws.Cells(1, 2), Ws.Cells(Ws.Cells(65536, 2).End(xlUp).Row, 7))
ReDim Preserve tabl(UBound(tabl) + 1)
Else
tabl(UBound(tabl)) = Ws.Range(Ws.Cells(2, 2), Ws.Cells(Ws.Cells(65536, 2).End(xlUp).Row, 7))
ReDim Preserve tabl(UBound(tabl) + 1)
End If
End If
Next Ws
ReDim Preserve tabl(UBound(tabl) - 1)
For i = LBound(tabl) To UBound(tabl)
Sheets("Compil").Cells(Sheets("Compil").Cells(65536, 2).End(xlUp).Row + 1, 2).Resize(UBound(tabl(i), 1), UBound(tabl(i), 2)) = tabl(i)
Next i
End Sub