Auto fill down of vlookup formula

Hi there, hope someone can help with this.

I have a spreadsheet which is essentially a list or database with columns A-N. Four of the columns have regular formulas while two of them have vlookup formulas referring to another sheet. What I want to be able to do (probably using VBA) is that when I start typing data in a new row in column A for example I want the formulas including the vlookup formulas to fill down into the row I can currently typing into. I already know that the "extend list formats and formulas" option doesn't work because it doesn't like ranges.

Is this possible?

I think I could utilise the piece of code below which I've pulled from another source, but I don't know how to do this when I enter data into a cell in a particular column.

----------------------------------------------
Worksheets("Fifty one").Range("I" & Rows.Count).End(xlUp).Copy
Worksheets("Fifty one").Cells(NewRow, 9).PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
-------------------------------------------------

Can anyone help me?

Please let me know if a problem of similar sort has already been solved and point me in the direction of it.

Thanks in advance,

Mike

Sorry, forgot to mention. I have attached an excel example which I hope will assist anyone in helping me along

Thanks

Mike

Sorry, forgot to mention. I have attached an excel example which I hope will assist anyone in helping me along

Thanks

Mike