vba code for vlookup for multiple column

[arunrana2004]

Hi, I'm Beginner of VBA, pls help me create the VBA for VLOOKUP for following data.

I've Book1 having 2 sheets. Sheet1 having the raw data, I want to create VLOOKUP in sheet2 to culled out the data from sheet1.


Sheet1

A B C D E F

Emp no. Emp Name Designation Date of Joining Department City

122233 abc Manager 13-06-14 Production Delhi

122234 def Executive 12-01-13 Sales Mumbai

122235 ghi President 13-01-13 Marketing Kolkata

122236 jkl officer 14-01-13 Operation Chennai

122237 mno Manager 15-01-13 Research New Delhi

122238 pqr senior Manager 16-01-13 Legal Mumbai

122239 stu senior officer 17-01-13 Development Kolkata

122240 vwx officer 18-01-13 Sales Delhi


My requirement are here-under-

When I type employee no. in any cell of column A in sheet2, Emp. name, designation, date of Joining, Department and city should be populated in corresponding cell of column B, C, D, E and F respectively.


Pls also see that vlookup formula should be hidden. also note that data in sheet1 can be go upto 5000 rows.


Pls help me.....

 

[Luke M]

Formula in cell B2 of Sheet2 would be:
=IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:B$5000,COLUMN(),FALSE))

Copy down and to the right as needed.