Renato,
A melhor solução neste caso, seria usar o procv pois no caso de uma macro, você depende de uma ação pessoal.
No caso do PROCV, se houver uma alguma alteração será modificado simultaneamente.
Cod Nome Contrato
1 A =procv(A2;Plan1!A1:B3;2)
2 B =procv(A3;Plan1!A1:B3;2)
Indicação: Usa $ para fixar a matriz ....;Plan1!$A$1
B$3....
Duvidas estou a disposição!!!!!
Espero que assim resolva a sua necessidade, abraços!