Once the value match then copy from Sheet 1 specific cell & paste to another Sheet 2 a specific cell

[Dharmesh Sabalpara]

HI There,

Need Urgent Help to Build a Code for one Work project
workbook I need once cell value Matched wi Once the value match from Sheet 1 Cell5 to Sheet 2 range (A4:A7) then copy from Sheet 1 Cell ("C9", "E9","G9") a specific cell &
paste to a specific cell Like Below Example
Sheet 1 Cell value is "Rajesh"
In Sheet 2 Once the value is matched then on the same line a specific cell ("B5","D5","F9")

I already tried Below Code

Sub findAndCopy()
Dim foundCell As Range, sh1, sh2 As Worksheet

'Set sheets
Set sh1 = Sheets("Test-01")
Set sh2 = Sheets("Test-02")

'Find string in column C of Sheet2
Set foundCell = sh2.Range("A2:A5").Find(sh1.Range("D5").Value, , xlValues, xlWhole)
If Not foundCell Is Nothing Then 'If match cell is found
sh1.Range("C9", "E9").Copy
foundCell.PasteSpecial xlPasteValues
foundCell.PasteSpecial xlPasteFormats

Application.CutCopyMode = False
Else
Call MsgBox("Not found the match cell!", vbExclamation, "Finding String")
End If
End Sub

 

[Joe4]

Welcome to the Board!

See if this does what you want (it seems to work for me when run against the example you posted):

Sub findAndCopy()
Dim foundCell As Range, sh1 As Worksheet, sh2 As Worksheet

'Set sheets
Set sh1 = Sheets("Test-01")
Set sh2 = Sheets("Test-02")

'Find string in column C of Sheet2
Set foundCell = sh2.Range("A2:A5").Find(sh1.Range("D5").Value, , xlValues, xlWhole)
If Not foundCell Is Nothing Then 'If match cell is found
    sh1.Range("C9:G9").Copy
    foundCell.Offset(0, 1).PasteSpecial xlPasteValues
    foundCell.Offset(0, 1).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
Else
    Call MsgBox("Not found the match cell!", vbExclamation, "Finding String")
End If

End Sub