Sub test()
Dim valSemaine
valSemaine = Feuil7.Range("P28").Value
Dim r As Range
With Feuil9.Range("B:B")
Set r = .Cells.Find(valSemaine)
If r Is Nothing Then
MsgBox ("Not Found")
Else
MsgBox (r.Address)
r.Activate
r.CurrentRegion.Select
r.CurrentRegion.Copy
End If
End With
End Sub