Hello,
I have a home-grown Excel Schedule that I use to schedule 200+ employees on a biweekly basis.
The MASTER tab is the posted schedule, tabs 1-SUN through 2-SAT are the daily assignment sheets, the FORMULAS tab simply shows what formulas are used, and the AUTOFILL tab is where I place prior year data to fill info on the daily assignment sheets.
Each daily assignment sheet has a number of different templates that I use throughout the year. After I save a new file, I go through each sheet and delete the entire row of the templates I will not be using for that schedule period.
Currently it is set up to only allow those who are available to be scheduled on any given day, to auto populate their requested out time, to flag if the shift start time is earlier than the availability, and to turn names red if they have been scheduled more than once on the same day.
I am looking for a way to run a macro that will automatically fill the shifts with available staff, aiming to get between 5.5 to 7 hours per shift.
I understand this will require prioritizing which shifts get filled first.
What do I do to make this happen?
(I've only ever built two macros, both in use on this workbook, and they only delete rows based on cell color.)
For your reference, the excel file can be viewed and downloaded here until 10/31/17:
https://emaillynn-my.sharepoint.com...8wBFkxU7g&expiration=2017-10-31T07:00:00.000Z
I have a home-grown Excel Schedule that I use to schedule 200+ employees on a biweekly basis.
The MASTER tab is the posted schedule, tabs 1-SUN through 2-SAT are the daily assignment sheets, the FORMULAS tab simply shows what formulas are used, and the AUTOFILL tab is where I place prior year data to fill info on the daily assignment sheets.
Each daily assignment sheet has a number of different templates that I use throughout the year. After I save a new file, I go through each sheet and delete the entire row of the templates I will not be using for that schedule period.
Currently it is set up to only allow those who are available to be scheduled on any given day, to auto populate their requested out time, to flag if the shift start time is earlier than the availability, and to turn names red if they have been scheduled more than once on the same day.
I am looking for a way to run a macro that will automatically fill the shifts with available staff, aiming to get between 5.5 to 7 hours per shift.
I understand this will require prioritizing which shifts get filled first.
What do I do to make this happen?
(I've only ever built two macros, both in use on this workbook, and they only delete rows based on cell color.)
For your reference, the excel file can be viewed and downloaded here until 10/31/17:
https://emaillynn-my.sharepoint.com...8wBFkxU7g&expiration=2017-10-31T07:00:00.000Z
