Need to create new sheet based master template and copy some parts over?

I have a master sheet. I want to be able to have a macro-assigned button which I can press which will create a new sheet which is a replica of that master sheet. Additionally, I want certain portions of that filled out master sheet moved onto the 2nd sheet. So for example, if I have a to-do list, I want that to be able to check box it as complete if it is completed, and those that are not checkboxed get copied onto the new sheet.

Example if I have written on the sheet "Eat, drink, sleep" but I have a checkbox which I check for eat and drink, then I want to be able to make a new sheet , sample template with the sleep carried over.

anyone help? what can i do in the vba?

Are you able to load up a sample spreadsheet with just a few lines of anonymised data?

Putting checkboxes on may not be practical, but you could have a column on your master sheet with a column called "Completed" which you could just put a Y into.

There might be some more questions, but having a sample spreadsheet would help to understand your exact needs.

Sure I will load up an example. I want only the checked box information (maybe there is a way I can say if "x" is true then it moves onto the new template once I hit the button)?

I just used a checklist I found online but will probably base mine off of that.

edit: basically, I want that checklist to be a day-to-day type checklist where I can update and change the rows as necessary. and once I hit create a new sheet, I want that previous sheet locked and only allow myself to edit the new sheet.

Thanks for that. I think I've seen this one (or something similar) before.

The checkboxes aren't "real" ones so those will be OK.

Will create some sample code for you to allow you to carry forward unfinished tasks each day.

Yeah it's a generic one online. Appreciate your help. I am currently learning VBA and I can write some code like loops and such, but I don't really know to go about this.

Can you explain by what you mean these are not real ones? Also, I can close the topics but double clicking the top part, but is it possible to make it single click? Same with the checkboxes, instead of doubleclicking is it possible to make it a single click?

Appreciate your help!

I've written some VBA which you can put into a new module:

Please Login or Register  to view this content.

You'll need to add a button to your first checklist sheet and assign this as the macro. The button will be copied to each new sheet.

There are a few things to note:
- I haven't given each new sheet a name so they just get appended with a new number. You might want to think about numbering (maybe a date?)
- I've taken the approach of just copying the whole sheet then deleting the completed rows
- You might want to think about what happens when you have loads of sheets - this might become a bit unmanageable. Personally, I always like to keep my data on a single sheet then use filtering or pivot tables for reporting. Once you've split everything out onto separate sheets you lose that facility - I've had several occasions where I've had to consolidate data back to a single sheet for my clients for that very reason.
- I switch on protection in the code, but the cells on the original checklist aren't protected and I didn't want to change this in case it impacted on the original code. It might be something you want to have a play around with.
- The check boxes are just characters which have been set with a Wingding font to make them look like check boxes. Its actually quite a neat solution and allowed me to just check for the letter R in the cell (which is the Wingdings 2 equivalent of a completed check box). I'll take a look at the code in the template to see how they're working with the double click.

Hopefully that's enough to get you started, but do have a think about your approach. It's a lot easier to change your mind now!

I'll get back to you on the double-click thing when I've had some time to look at it.

Really appreciate your help, thanks!

My pleasure!

I thought about the "double-click" problem, and realise that, because the check boxes aren't "real", you're a bit stuck with it I'm afraid. The double-click is a worksheet event which triggers the change from one Wingding character to another.

Good luck with the development - post more questions if you get stuck.

Excel is a constant learning process and it's great to help each other. If I've helped you today, a click on the star on the left is appreciated.