Two possibilities:

1) Put your macro  in its own workbook, separate from the data  worksheets. You
may need to tweak the code to operate on the active workbook, not ThisWorkbook.
You could even save it as an Excel add-in.

OR...

2) After your macro has done its retrieval and analysis work, and saved the
workbook as today's file, have it Save As again with the name for tomorrow's
file. Then rename the sheets so that Sheet2 becomes Sheet1 (you'll have to
rename Sheet1 first) and clear every sheet  but the new Sheet1.