Chris,

As soon as you have created your new workbook save it with your chosen
name and then read the new name into a string variable which you can
then use to activate that workbook when each time that it is necesary.

dim NewwbName

NewwbName=ActiveWorkbook.name

Windows(NewwbName).Activate '(when required)


As far as content of your macro is concerned you could create a new
workbook with only a single sheet and then just copy the data across
rather than deleting all of the spare sheets.

Alan