I have an excel file that has a template (user fills out) and then with a
button on that template. The information is then transfered to 4 other
sheets (Summary SHeets) for record keeping purposes as well as makes a copy
of that template and renames the sheet and then deletes the information on
the previous template to make it usable again.

My problem is that when I create a copy, I cannot reference the formulas to
the Summary Sheets as they are direct references to the template. How can I
change the formula according to the new, copied sheet name? (Also, the user
enters in the tab/sheet name every time into a cell...)

Thanks so much, I've been working on this for hours!