I left out a couple of considerations in my previous example.
For instance:

Keep in mind that your macro  is running in the first file,
but when you copy a sheet  to a new workbook, the "focus"
changes to the new workbook.
Remember that Excel uses "ThisWorkbook" to mean the
workbook which has the macro currently running.
"Activeworkbook" is the workbook  that currently has "focus".
So, let's say that your Excel file with macros is called "File1.xls".
When the sheet is copied  to a new book, and the user  decides to
"saveAs" and calls the new book "NewFile.xls".
Then:
Thisworkbook.name is "File1.xls"
Activeworkbook.name is "NewFile.xls".

Just to keep it straight...