PasteSpecial causing RunTime error 1004 & autofill doesn't stop at last line

I am attempting to add a line and insert a VLookup for all the account numbers in column A (the Openworkbooks will be changed to filter through a list on a loop opnce I have this so the row count will vary). Currently the autofill isn't stopping and completing the whole of Column B (which needs to be addressed as it will paste #N/A all down the page) but the main issue is copying the column and pasting values instead of the formula as when this is closed it can't have formulas in it.

Can anyone suggest any help with the code please?

Please Login or Register  to view this content.

You're calculating lastrow before you open the workbook, so you get the last row (presumably) of your lookup table instead - which you probably do want to use for your formula. Try this:

Please Login or Register  to view this content.

Yes, that's where the name comes from.

A couple of issues in there - I'd suggest this:

Please Login or Register  to view this content.

It replaces the formula with the calculated values (the equivalent of the Copy & PasteSpecial you had).