VBA to automatically populate rolling rota - Help?!

[samboco]

HI,

I'm looking for a VBA solution to automatically populate an on call rota please.

The rota should be filled according to the following rules:

• Only 1 staff member for each day
• Staff member can't be on 2 consecutive days
• weekdays are allocated separately from weekend days. This is to ensure fair distribution of weekend days but must be mindful of Saturdays following on from Fridays.
• If the number of staff on the on call list (call_list) is divisible by five then the weekday allocation should be given in such a way that the days allocated roll i.e. staff member is not constantly on a Monday week to week but will be Monday one week, a different day the next etc. Where the list is not divisible by 5 there will be a natural roll to facilitate this.
• If the number on the call list is divisible by 2 then the weekend days will need to be allocated in a way that facilitates a varied weekend day. If this is too fidgety to achieve then it's fine, I can manually change these.

I have a workbook that has my current method to achieve this (see images).

1. A date is entered in Rota_start ('Order of play'!A2) which then creates the date range in the rota worksheet.
2. The name of the person to start the weekday rota is selected from the drop down list. As is the person for the weekend rota. These names would be the people following on from the tail end of the previous rota.
3. Rota populates with names based on the cell formulas.

Whilst this workbook does what I am trying to achieve, it is limited because it works for the number of people on the call list.

I would like for a VBA solution that works dynamically should the number of people on the call list increase or decrease.
Is this possible?