Copy hyperlink file to another location and give it a new file name

I have a spreadsheet (Sheet1) with a bill of material list... each component part number (in column B) has a hyperlink to its standard drawing file on the network. Our component drawings are not all stored in the same folder on the network so each hyperlink path is different. When we need to make a custom version of these drawings, we manually search for and copy each original drawing to a new location (customer job folder) on the network. In a typical BOM, we may have as many of 40-50 drawings that need to be copied over and modified. This process is time consuming so I wanted to try automating, if possible. Basically, I would like to prompt the user to locate the customer job folder (existing) with a browse dialog box. Then, reading column B, have the program make a copy the actual hyperlinked files from column B, give each file a new file name (derived from column C) and put the new file in the chosen job folder, making the new file a hyperlink for value in column C. Is this possible? Any help would be greatly appreciated!