Au secour !!! Cela ne fonctionne pas !

Option Explicit
Public Sub renvoitableau()
Dim i As Integer, derligne As Integer, j As Integer
Dim c As Range

Dim datanom As New Collection
Set datanom = New Collection


On Error Resume Next
For Each c In Range('a2:a' & Range('a65536').End(xlUp).Row)
    datanom.Add c.Text, c.Text
Next c

With Sheets('feuil2')

For j = 1 To datanom.Count
    i = 1
    derligne = .Range('a65536').End(xlUp).Row + 1
    For Each c In Range('a2:a' & Range('a65536').End(xlUp).Row)
        If c.Text = datanom.item(j) Then
            .Cells(derligne, 1) = c.Value
            .Cells(derligne, i + 1) = c.Offset(0, 1)
            .Cells(derligne, i + 2) = c.Offset(0, 2)
            .Cells(derligne, i + 3) = c.Offset(0, 3)
            i = i + 3
        End If
    Next c
Next j
.Select
End With
End Sub