VBA help - Code to update excel links when file path changes

Hi all,

I am really struggling to find an answer to my question so hoping someone here might be able to help.

I have a set of Excel documents set up as a template for new jobs each time a new job comes in. When we get a new one in I copy and paste the "template" file and rename it as the new file name.

The file is set up with subfolders, each containing an Excel workbook and they all link to each other (about 50 separate workbooks within the template file) - obviously these links work fine within the original template file but as soon as I copy and paste then none of the links update to the new file path and instead still link back to the original template file.

Can someone tell me if it's possible to have a macro that will automatically update all 50+ Excel workbooks when I copy and paste the template file to reflect the new file path, rather than me having to go into each separate workbook when I copy it over and restore the links manually?

Thank you in advance!!

Thanks for your reply.

The files are all contained in one main folder, but are divided up into separate sub-folders.

Hard to say where the links are, but I guess the main one and a good example would be a main "Client Details" sheet which all other sheets link to so you don't have to manually enter job name, title, client name, contact price etc. into each separate sheet.

Another example is we have a main summary sheet (which also links back to the Client Details sheet) which several other subcontract agreement sheets link back to, so when I put in a price in the subcontract document it shows up on the summary sheet etc., and will also link to a cash flow sheet.

I hope that makes sense - please let me know if you need further clarification.

thank you!

Sorry, I'm not sure what you mean by an Excel sample?

Well all the files are already set up and have been in use for some time. The problem is the links breaking when the file is copied over. I just want to know if there's something that can automate the updating of links to the new file path.