ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC37,'PRICING TEMPLATE II .xls'!Cat_Code,2,0)"
LastRow = Cells(Rows.Count, 37).End(xlUp).Row

ActiveCell.Copy _
Destination:=Range(ActiveCell, Cells(LastRow, ActiveCell.Column))

"Matadi Tappe" wrote:

I have this code:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC37,'PRICING TEMPLATE II .xls'!Cat_Code,2,0)"
Used Macro recorder and it works to an extent. I have to manually copy and
paste to the last text entry in cloumn AD. I can not modify code to go as
far as the last occupied cell in cloumn AD and stop. Any sugestion as to the
routine to use for this. The Answer is returned to Cell A2 on down.

Thanks
--
Tappita