translate an Excel 4 macro to VBA

I wrote this or probably filched bits and cobbled them together about 20 years ago and it has served me so well all this time running in various guises or often just on it's own, but I would love to see it in VBA. The file is a cut down version of a worksheet I use to copy product information (usually prices but sometimes text information) from suppliers files to my own files. There are 2 macros, the first in VBA sets up the names used to the top of the sheet to work downwards, the second "Macro 5" is the Excel 4 macro.