Option Explicit
Sub test()
Dim a, b(), i As Long, j As Byte, x As Long, n As Long
With Sheets("DONNEES LIS").Range("I1").CurrentRegion
a = .Value
x = Application.Count(.Columns("f:i").Cells)
End With
ReDim b(1 To x, 1 To 4)
For j = 6 To UBound(a, 2)
For i = 2 To UBound(a, 1)
If a(i, j) = 1 Then
n = n + 1
b(n, 1) = j - 5
b(n, 2) = a(i, 2)
b(n, 3) = a(i, 5)
b(n, 4) = a(i, 4)
End If
Next
Next
With Sheets("ADVERSAIRES").Cells(1)
'.Cells.Clear
.Resize(1, UBound(b, 2)).Value = Array("Equipe", "Club", _
"Dérogation", "Local")
.Offset(1).Resize(n, UBound(b, 2)).Value = b
With .CurrentRegion
.Font.Name = "calibri"
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
.VerticalAlignment = xlCenter
With .Rows(1)
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 40
.Font.Bold = True
.BorderAround Weight:=xlThin
End With
'.Columns.AutoFit
End With
End With
End Sub