I am using Excel 2007.

My automation is set and basically updates the Template with new data every month when they open the workbook (after they receive an email that the data has been updated). It also has a combobox/button which allows them to display previous months' data on the Template.

I am trying to add an additional button which will create a new workbook and copy the report from the Template over to the new workbook. This way they can just send the relevant data to other people and not the Template which has all the macros and such.

Creating and copying is the easy part. However, every time I have the macro create a new workbook the name changes Book1, Book2, Book3, Book4, etc. Is there any way to name the new workbooks being created beforehand so the macro refers to the correct workbook? Or to store the name of the new workbook created into a variable so I can just reference that variable in the macro and it always refers to the correct one in cases where multiple workbooks are created?