Sleeper: Staffing Roster Automation

[The Tamer]

Hi

I'm writing this on behalf of TWK who'll be registering shortly.

What I have is a rather large number of work shift codes which indicate what weekend days a given staff member will be working over a four week period

There is a list of employees on one sheet that has a code next to each employee, and what we?re hoping for is that a macro will look at the employee?s shift code and then assign him the appropriate series of shifts in the roster sheet.

I have attached a copy of the w/book for you to see what the roster looks like. But the OP is happy to hear any suggestions on using an entirely different suggestion if it?ll be easier but still give him what he needs.

Many thanks, and good luck TWK.

Tamer

[Paleo]

Hi Tammer,

I didnt understand the logic, nor which one is the shift code (FTE, ROTATION, WEEKEND). Thought its WEEKEND but in this case I didnt understand its logic. Can you provide more info on this?

[Paleo]

Sorry, only now I have seem the Code Explanation sheet .

What a goofed

[johnske]

Don't feel bad Carlos, I had a look at both sheets and I still didn't understand, so I thought I'd leave it for someone that may.

I would like to know more before I could even start...
1) How long are the shifts? e.g. midnight to 8am, then 8am to 4pm, then 4pm to midnight, or, maybe 6 hour shifts?
2) How many workers are required for each shift?
3) If there are more available for a shift than the number of positions available, what criteria is to be used to assign the workers to the shift? (I assume that's part of the code given) e.g. Seniority? By rotation?
Randomly?

[Paleo]

Hi John,

you right. We need some more info. I have started it but without knowing the answers for your questions is almost impossible to solve it. What I did so far (thats almost nothing) was:

Private Sub cmdWeekend_Click()
    Sheets("Staff").Activate
    Dim Folga As String
    For i = 2 To Range("E65536").End(xlUp).Row
        Folga = Range("E" & i)
        If Mid(Folga, 1, 1) = "T" Then
End If
    Next
End Sub

[The Tamer]

Both,

Thanks for answering this for me (well, TKW really).

I can answer one of your questions: How long are the shifts? Well, many of the employee codes have an A, B, E, or D after them and these indicate the shift times as per the info on "Code Explanations" on columns E & F

As for the other questions. No idea.

I'll get bring TWK's attention to your answers, and perhaps he can throw some light on this for you.

Thanks

Damo

[Paleo]

Hi Damo,

ok, thanks and lets get it solved to TKW or TWK (you used both).

[The Tamer]

Sorry, it's TWK

Thanks again. Sent him an email to check the site earlier, so he should get here soon I guess.

[TWK]

Hi All. Thank You for Working on this. Sorry it took me awhile to post.
First Let offer a little more exsplanation. What I'm hoping to do is to be able to assign the codes to people and have there names print out on the second sheet for each schedule. If then for example a person working every weekend(EW0) decides that they want to switch to every third weekend(ETW) then I can simply change their code and there name will print out on the schedule sheets accordingly. It would also be nice to have there names print out according to there shift. Days(7am-3pm)Evenings 1 (3p-7p)Evenings 2 (7p-11p) and Nights 11p-7a. Evenings is split since most people work 12hr shifts Their names would be on both shifts. For example someone working an "A" (7a-7p) would have there name under Days and Evenings 1. and the totals would show for each shift at the bottom. If this last part would be too much work I would be fine with just getting everyones names under the right dates. Hope this helps with some of your questions. The program won't be responsible for deciding what weekends are short or who needs to be assigned where. It strictly goes off the infor I put in for the weekend code.