I have an Excel file that is a summary file of select pieces of data from 40-60 other Excel subsidiary files. Every month we add a few new files that need to be added on to this summary tab.

The thought process I was using was to do a macro that takes a row of data linked to a template, copy & paste the row, then replace the links on the new row from the template file to the newly created file.

I am trying to figure out a simple (BY SIMPLE I MEAN VERY SIMPLE) way for people in my group to be able to replace this link. I have created a macro that does all of the steps (including an input box for them to enter the new file name) EXCEPT, I cannot figure out how to replace with a new file name.

Is there a way to do something like Find: s:\template.xls
Replace: =A1

Please help me!