Hi Norie,
Thanks for taking the time to have a look.
I have moved the code onto the worksheet module. Everything is working fine now, there was one other problem I had not named the sheet for the range that the vlookup function returned, eg A1:A5 should have been Sheet2!A1:A5.
Many thanks for all your help