Copy template sheet to a new sheet with the code in the teimplate sheet

From a coding standpoint, I don't think your approach is ideal. Copying the visible sheet and pasting it won't carry over any VBA. On top of that issue, I think the worksheet event you're using to apply the new name leaves a bit of room for improvement. Have you considered just using a command button, rather than trying to key everything off of changes in R1? In the attachment, I created a command button next to R1 on the Template sheet that creates a new sheet based on the template and using the name in R1. The code is as follows:


NOTE: You should remove your existing code before clicking the button to run the code below, otherwise they'll get in each other's way

Please Login or Register  to view this content.

The current version will create a complete copy including the "Create New Sheet" button. This will enable you to create more new sheets based on whichever sheet you click the "Create new sheet" button from. If that is not an effect that you want, remove the apostrophe from the line noted in the code and the newly created sheets will not have the command button to create more new sheets, meaning the user can only create sheets from the template page. I hope that makes sense? I would advise removing the apostrophe and ensuring that all new sheets come from the template sheet only, but I couldn't tell from your post if you wanted the ability to duplicate sheets other than the template sheet. Take a look at the attachment to see if it's instructive:

Glad to help, good luck!