Sub Essai()
Dim Plage As Range, Cellule As Range, Trouve As Range, maplage As Range
Dim Debut As String, Fin As String
With Sheets("F1")
Set maplage = .Range("A6:c6", .Cells(Rows.Count, Columns.Count))
Set Trouve = maplage.Find("Prenoms", LookIn:=xlValues, lookat:=xlWhole)
If Not Trouve Is Nothing Then
Debut = Trouve.Offset(1, 0).Address
Fin = .Range(Split(Debut, "$")(1) & Rows.Count).End(xlUp).Address
Set Plage = Range(Debut & ":" & Fin)
For Each Cellule In Plage
Feuil2.Range("F" & Cellule.Row) = Left(Cellule, 1)
Next
End If
End With
End Sub