Creating new worksheets from a template named according to cell values in a spreadsheet

[tgray58]

I am new to VBA and struggling to get a macro to create new worksheets (copied) from a template and named using cell values from a summary worksheet. The new worksheets will be added to the existing workbook and the cell values are from a variable named range that is made up of a concatenation of a user entered LastName, FirstName contained within a worksheet called Summary.

The idea is for a person to add a volunteer name to the summary worksheet and have the Volunteer Named worksheet created, their name added to it and have the worksheets autosort in alphabetical order. The last part I can do and I have had partial success in copying their names to the new worksheet and in creating the worksheets themselves but I have not been able to tie it all together. This is for a local non-profit that still uses manual methods and I have delivered a spreadsheet version to them that works except they are still required to create the described macro functions manually. A simple example spreadsheet is attached.

Steps
1. First Name and Last Name Entered in the Summary Worksheet Columns A & B respectively beginning with row 4. In this example the first two rows have been populated and the Volunteer worksheets created. The third volunteer is in process and their named worksheet has not been created.

2. A Warning in column D indicates "ADD WORKSHEET" to let the person know that they need to create the new volunteer worksheet

3. The blue update button is pressed starting a macro - This can be done following each new person added or batch processed

4. The macro does the following two actions:
*Copies the worksheet called "Template" and names it with the cell value in the (Summary worksheet) column C - As shown in the example the name of the third volunteer worksheet would be the cell value of cell C6. Each time the macro is run it should check to verify that a valid name (Not a Blank) exists, it should then check the existing sheets to make sure it has not been created, if it does not exist the new worksheet is created.
* The cell value in the Summary worksheet C6 gets copied into cell B1 on the newly created worksheet of the same name.

Then the macro loops to the next row and continues until it encounters a valid blank (end of the variable length list) If it can integrate the SORT VOLUNTEERS Macro that would be a bonus. The individual volunteers update their hours on their named worksheets. New Volunteers would be added as they come on board. Any help would be greatly appreciated.

Tom

[J450n]

Hi tgray58.

I've put together the following code based on the description above which has 1st names in colA, last names in colB starting from row 5. This should help you, i've tested it and works fine on my machine.

Please Login or Register  to view this content.

you may want to also consider making the concatenation of the names part of the macro too, since less for the users to accidentally delete and cause further problems

Regards

J450n

[tgray58]

J450n -

Thank you for the time you spent on this. I was able to do a little additional modifying to it so that I could name the worksheets either with the First Name LastName format as you provided or with the LastName, FirstName format. I also commented out the auto naming of cell C4 on the new sheet wthe sheet name to and used a diffent method so that the worksheet name can continue to be changed and linked to the C4 cell. This does exactly what I was hoping and the error messages were fantastic. I also added a select function for the summary page so that within each worksheet update the Summary page becomes the final landing page. A few work sheet names were changed as well to allow sorting to suit my needs and I added another function that sorts the worksheets forward and backwards. Attached is the working spreadsheet so that you can see it operating. Feel free to share with others as you were so kind to help me. Thanks again.

Tom

Here is the code with changes:

Please Login or Register  to view this content.

[J450n]

You're welcome Tom, glad I could help.

You'd best edit your last post and include the code in tags and set the prefix to Solved tho, to keep the moderators happy.

Regards

J450n