Automatically create new worksheet template daily with current date

I've created an Excel workshop to allow people track the different work activities they engage in over the working day (it's for a study we are doing). The idea is that everyone in my department will have a copy of the worksheet open on their computer at their desk and will record in realtime the different work activities they do over the day. I'm trying to make it as foolproof as possible by locking down everything except the cells they ned to complete.

My problem is that I want Excel to create new worksheet everyday that is labelled with the current date. However, I want this worksheet to be the template I created (e.g. column headings, formulas, etc) rather than a completely blank worksheet. I also want Excel to either do this automatically when the workbook is opened each day or else have a button on the workbook that the person clicks to do it.

I'm relatively new to Excel so have just been learning as I go along. I also don't have any knowledge of Visual Basic.

I've attached a copy of the workbook (the password for both the sheet and the workbook is thescientist

Any tips or help with this would be much appreciated.

Hi try this

I've named the sheet that you want to Copy as "Master" added a Button "Admin"

Added a New Sheet called Admin and added two buttons and assigned Macros to them

Button1. Amend "Master" Sheet password "lemonade"
Button2. Amend "Fields" Sheet password "lemonade



In the VBA editor, find the "ThisWorkbook" object and dbl click. You will get a window titled "bookName - ThisWorkbook". In the left dropdown select workbook and you should get a workbook open event procedure.

Try this and see if it does what you want.

Please Login or Register  to view this content.

I also added to Modules in the VBA

Module1

Please Login or Register  to view this content.

Module2

Please Login or Register  to view this content.

Hi,

Thanks a million for that. That's even better than I was hoping for. I seems to work perfectly. When I run it, it creates a new sheet labelled with the current date, exactly as I was looking for.

I assume that when the date changes (i.e. tomorrow) it will add another sheet with that date and so on each day so that the workbook will have a range of sheets (one for each day). Out of curiosity does it create the new sheet automatically when the date changes or when the workbook is first opened on a new date (either way is fine for me).

Thanks as well for adding the admin button, that's really handy. Is it possible to hide the admin sheet tab given that I can access it though the button? I've tried hiding it but every time I reopen the workbook it's visible again. It's not a big deal though.

I've attached the file again as I've made a few changes to the "Fields" sheet

Again, that's for all your help with this.

Hi there,

If you're in Crumlin Hospital then you're just up the road from me (in Rathgar)

Take a look at the attached workbook and see if it does what you need - it uses the following code:

Standard VBA CodeModule

Please Login or Register  to view this content.

Worksheet VBA CodeModule

Please Login or Register  to view this content.

I've simplified the code for your Calendar form by using a Class and Collection approach - this avoids the need to have separate (i.e. 42) routines for each of the various Date buttons on the form.


Hope this helps - please let me know how you get on.

Regards,

Greg M

That's absolutely perfect.
Again, thanks a million for your help with this.

Your welcome,

also excellent design by Greg M

Hi again,

Many thanks Toonies for your kind words!

This is an interesting project so I've been playing around with it, and the latest version is attached.

This version doesn't require an Administrator to use passwords, but rather the buttons associated with Administrator tasks are hidden unless the current UserName is included in the "approved list" of Administrators. This list is maintained by editing the highlighted values in the statement below:

Please Login or Register  to view this content.

Hope you find the attached interesting and/or useful.

Regards,

Greg M