Copying data returning an error in Sheet1 to next empty cell in specific column of Sheet2

Excel Help.xlsx

Hi,

I have been reading through some VBA guides but I can’t seem to work out quite what I need to do to solve my problem so I’m wondering if someone could help?
The problem effectively revolves around two sheets, call them Sheet1 and Sheet2 for the purposes of this.
On Sheet1 external data is copied into column A in the form of a list of names. Column B is a VLOOKUP with finds the codes (In Sheet2) assigned to the specific names. For clarification, the list of these names and codes are located in Columns A&B of Sheet2 respectively.
If a name is copied in which has not yet been assigned a code, then Sheet1 Col B obviously returns an N/A error.
What I would essentially like to be able to do, is run a macro directly after the data is copied into Sheet 1 that assigns any entries that have no code, with a new code.
If the codes are located in Sheet 2 Col B, I can extend this series as long as necessary. So when an error is returned in Sheet1 Col B, the name in the adjacent cell from Sheet 1 Col A is copied to the next empty cell in Sheet 2 Col A – thus effectively assigning it a new code.
I’ve tried to explain this as best I can, and attached an example of what I'm trying to achieve, so I live in hope someone might be able to help?
Kind Regards!