Try using a VLOOKUP worksheet function it may be quicker and easier than coding a macro.

You'll have to put the VLOOKUP in the a cell next to the old header and then you can cut and replace the Old Header with the New Header.

=VLOOKUP(A6,[sourceworkbook.xls]Sheet3!$A$1:$B$25,2,FALSE)

change the reference in red to the sourceworkbook, sheet and range where the new headers need to be pulled from.