Copy Template Worksheet And Rename With Custom Name

[peter renton]

I have a calendar on the book which selects the corrospending sheet when a date button is pressed.

Is there a way for a similar system to generate/insert a clone of a blank template sheet and name it acording to which date/month/year button is pressed?
At the moment i just copy the sheet one at a time and rename each sheet (the name has to be exact for the selection calendar to find them)

Once again thank you for your help

Peter

[peter renton]

Sorry i have attached a sample work book showing the select calendar

(Also sorry for attaching this question to an earlier one)


Peter

Does anyone know if what i am asking is possible? or should i be looking at different way to do what i need ??

Thanks for lookinh

[bakerman]

Dear Peter,
Sorry for the delayed response, but this is what i came up with.
If you select a date from an excisting sheet you will be directed to that sheet (the old functionality).
If you select a date from a non-excisting sheet you will be prompted wether you want to make a new sheet. Incase you selected a wrong date you can cancel at this point by clicking No.
Else the Template-sheet will be copied and renamed to the selected date.
The only thing you have to do before running this is that you have to locate the Change_Sheet sub in the calenderform and change the sheetname next to SheetToCopy.

P.s.
You will also notice that i've made some other changes to the original code.
Instead of repeating the same code 42 times for each button i have made one mastercode (Change_Sheet) which recieves an argument x.
In each Click-event from all buttons you refer to this mastercode and pass the argument.
The main advantages of this method are first of all you save a lot of lines of code and secondly if you make a change that applies to all buttons you only make the change in the mastercode instead of changing it 42 times.

[peter renton]

Hi Bakerman

Thats excellent, just two points
Could the newly generated sheet be inserted in date order? or at least after an active sheet?

Also The add sheet function will not work in shared mode, can this be altered or will have have to unshare to add sheets


By the way thanks for tidying up the code

Much apprieciated
















Thanks for tidying up the code,

Just

[bakerman]

Change these lines

Sheets(SheetToCopy).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = NameTest

to this

Sheets(SheetToCopy).Copy After:=ActiveSheet
ActiveSheet.Name = NameTest

About the sharing-unsharing
You could incorperate this in your code to perform this task automated, but then you should be shure you are the only user at that time otherwise it could get nasty.

[peter renton]

Once again

Thank you, its spot on

I think you are correct if its prone to failure to allow shared adding of sheets i will leave it as it is


Thank you very much for helping me with this


Peter