VBA to choose template, fill it in and then save workbook - impossible??????

Hi all,
I have spent around a month trying to find an appropriate solution to the task below but it’s been to no avail.

Background:
I have a workbook called ‘Data Sheet’ which contains a list of employee names (120+), which shift pattern they work, their managers names and individual stats based on their performance in a given quarter. I have spent a lot of time working on this so that these stats are pulled from various different sources rather than manually keyed – Progress!

At the end of the quarter every manager needs to create a workbook with a 2 sheet performance review for each of their employees. (i.e. 100 employees with 10 managers would result in 10 workbooks being created with 20 sheets in each). This process is currently manual and very time consuming.

To complicate things further, depending on what shift the employee works (D,R or DP) one of three different templates are needed.


Task:
I would like to have some VBA that does the following:
• Look to see how many employees in the ‘Data Sheet’ are managed by a particular manager.
• Of those employees, look to see if their shift is “D”, “R” or “DP”.
• Create a new workbook and copy the correct 2 page template (based on their shift) into it for each employee managed by that manager.
• Rename the first page of each template to the employee name.
• Rename the second name of each template to the employee name + “Disc”.
• Fill in each template using the relevant employees data contained in the ‘Data Sheet’ workbook.
• Save the work book as the manager name.
• Do the same for the next manager and so on.


I know this is a big task but I would really appreciate someone’s genius on this.

I have tried a lot of different coding and it’s now got to a point where I’m out of ideas and need to start again.

Thank you.

ps: I'm unable to upload any examples at the moment.

Hi JBeaucaire,
I have just been looking at your 'Data into template' code and it looks very close to what I am trying to achieve.
Would I be able to incorporate this along with additional if statements?
Or am I going down the wrong route?

Thanks.

I had seen and attempted to modify your coding previously but was overwhelmed by the task I am trying to complete. Thanks to your very clear coding I was able to create a template for every employee within the 'data sheet' very quickly.

However, I would appreciate some advice as I cannot decide the best way to choose a template? Do I write code to complete the tasks listed in my original post (in order) or is there a simplier way. I am very much a novice when it comes to VBA and I know you guys are consistently hounded with requests/queries but if you could point me in the right direction that would be a great help.