Compare cells and copy

[AOrat]

Hi,
Please help me write VBA code.In Sheet 1, in cell A4, I have a drop-down list with product names. I fill cells B4:D4 manually with numerical data. In cell B2, the formula returns a certain result.
There is a table in Sheet 2 in the range A2:E50. Column A contains the product names. I need to copy the value of cell B2 from Sheet 1 to column E in Sheet 2, to the row where the product names from cell A4 in Sheet 1 and the range A2:A50 in Sheet 2 are the same.

 

[bobaol]

try this. cheers!

Sub Macro1_copy_b2_a4_paste()
'''this is all from Record Macro (except for one line), not written by hand
'''assume you have sheet1 and sheet2
    Application.Goto Reference:="R1C1"
    Sheets("Sheet1").Select
    Application.Goto Reference:="R1C1"
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C1"

''go to h2 in sheet2, use formula to bring in cell b2 in sheet1
    Sheets("Sheet2").Select
    Application.Goto Reference:="R2C8"
    ActiveCell.FormulaR1C1 = "=Sheet1!RC[-6]"

''go to h4 in sheet2, use formula to bring in cell a4 in sheet1
    Application.Goto Reference:="R4C8"
    ActiveCell.FormulaR1C1 = "=Sheet1!RC[-7]"

'select column A to find the value from a4 sheet1
    Sheets("Sheet2").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Columns("A:A").EntireColumn.Select
''recorded looks like this...
''    Selection.Find(What:="a4 - product name", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
'''manually change this line to refer to cell h4, as h4 is the same as a4
    Selection.Find(What:=Range("h4"), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
''once found, move over 4 cells to column E
    ActiveCell.Offset(0, 4).Range("A1").Select
''use formula to refer to cell h2, as h2 is the same as b2 in sheet1
    Selection.FormulaR1C1 = "=R2C8"
    
''copy, paste special as values
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Calculate
    Application.Goto Reference:="R1C1"
End Sub

 

[AOrat]

Thank you very much for your help.