If name is X insert Y phone number from Z worksheet in Excel

I am trying to create a simple macro or VBA that will insert a phone number in column Y for value of someones name in Column X from a directory worksheet.

So for example, worksheet 1 (directory) has names of individuals and phone numbers:

John Smith 123 456 789
Joe Garden 555 555 555
Jill Spill 999 999 999

When creating a new worksheet 2, when I put John Smith in Column A, I want it to automatically populate 123 456 789 in Column B.

Thanks for the help!

1 answer

  • answered 2019-07-10 23:34 tmsdajpa

    Try this code and let me know if there's anything that I missed. Be sure to change Sheet4 to the name of the sheet that has the phone # list.

    Sub Macro12()
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet4!C[-1]:C,2,FALSE)"
        ActiveCell.Offset(0, 1).Range("A1").Select
    End Sub