VBA to Copy Sheet and Rename, with Sheetname used in Macro

Hi Guys,

So in my workbook, I need to be able to copy the first sheet exactly, but rename it with whatever the user wants, for which I've used this:

Please Login or Register  to view this content.

The trouble I'm having is that the Macro within the sheet I need to copy has a sheet reference, using the name of the first sheet, rather than an ActiveSheet type reference, like this:

Please Login or Register  to view this content.

So when I copy the sheet across, and run the macro, it runs it in the first sheet, instead of the new sheet.


Firstly, is there a more elegant way to copy the sheet?
And secondly, is there a way to get the macro to work on the new sheet, rather than the original?

Thanks,
Mike

Sure, here's the workbook.

Any improvement ideas would be welcome!

Bump, still looking for a solution.

Your request is not clear

Do not use Worksheets(1).
use either code name for the sheet , or refer it by its sheet name like sheets("Sheet1")
I used Me as a worksheet and creates sheet me.

Please Login or Register  to view this content.