تفضل اخى الكود كاملا
Private Sub CommandButton3_Click()
Dim C As Range, SH As Worksheet, WS As Worksheet, A As String
Set SH = ThisWorkbook.Worksheets("Sheet1")
If SH.Range("I4").Value <> "" Then
Set WS = ThisWorkbook.Worksheets("Sheet2")
A = SH.Range("I4").Value
Else
Set WS = ThisWorkbook.Worksheets("Sheet4")
A = WS.Range("L2").Value
End If
Set C = WS.Range("C:C").Find(What:=A, LookIn:=xlValues, LookAt:=xlWhole)
With SH
.Range("d6") = WS.Cells(C.Row, "b")
.Range("d8") = WS.Cells(C.Row, "d")
.Range("d10") = WS.Cells(C.Row, "e")
.Range("d12") = WS.Cells(C.Row, "f")
.Range("d14") = WS.Cells(C.Row, "g")
.Range("d16") = WS.Cells(C.Row, "h")
.Range("d18") = WS.Cells(C.Row, "i")
.Range("g6") = WS.Cells(C.Row, "c")
.Range("g8") = WS.Cells(C.Row, "j")
.Range("g10") = WS.Cells(C.Row, "k")
.Range("g12") = WS.Cells(C.Row, "l")
.Range("g14") = WS.Cells(C.Row, "m")
.Range("g16") = WS.Cells(C.Row, "n")
.Range("g18") = WS.Cells(C.Row, "o")
End With
End Sub