Autofill by value?? VBA or Formula????

Can you give a little more info. I don't know how many rows or columns were talking about.
Are you trying to transfer Title, Name, Age, Company, to another sheet when someone selects a cell on that sheet?

Yes mate, For example all the info is in Sheet1 from row 6 column B, to row 53 column J,

On Sheet2, you select company(info on column J, Sheet1) which will populate all matching that, column B Sheet1 into column B Sheet2, and column D Sheet1 into column C sheet2

many thanks

In sheet 2 Cell "B2" paste this formula:
=INDEX(Sheet1!$B$6:$B$53,MATCH($A$2,Sheet1!$E$6:$E$53,0))

In sheet 2 Cell "D2" paste this formula:
=INDEX(Sheet1!$D$6:$D$53,MATCH($A$2,Sheet1!$E$6:$E$53,0))

Now you can type the name of a company in Sheet 2, cell "A2" and the 2 values that are associated
with that company will show up.

Instead you could create a drop down list in sheet2, cell "A2", based on the names of the companies on sheet 1.
Go to sheet 1 and select "E6:E53".
Now select "Insert" in the main menu.
Scroll down select "Name".
In the sub menu select "Define"
In the window at the top type a name for the range. Make it "Company"
At the bottom of the window type this formula:
=OFFSET(Sheet1!$E$6,0,0,COUNTIF(Sheet1!$E$6:$E$53,"<>0"),1)
Click add

Now got to sheet 2,Select cell "A2" select "Data" then select "Validation"
For the validation select list and at the bottom in the formula bar type "=Company"

I hope this helps.