I started the code below but need more info to continue. I don't
understadn the Term "Shift staff". Also, is there a minimum number of
hours each employee need to work? I don't understand the supervisors
shcedule. Does the 10 employees include the supervisor or is the
supervisor in addition to the 10 other employees. Also how many
supervisors are there.
the code requeire the workbook to have at least on sheet Names
"Holidays". On this sheet put the word Holiday in cell A1. Then list
all the holidays in a date format starting in cell A2.
The code will delete all sheets except the sheet named Holidays and
create a 12 month clendar with the number of employees needed each
shift.
I'm thinking of rotating the employees but don't have all the
requirements. Usually you would have an employee work 5/6 days one
shift then have two days off and going to next shift.
VBA Code:
--------------------
Const NumberOfStaff = 10
Const FirstShiftRow = 6
Const SkipRows = 3
Sub GenerateCalendar()
'set up rows on worksheet where to start each shift
SecondShiftRow = FirstShiftRow + NumberOfStaff + SkipRows
'code assigns Employees in Order
EmployeeCount = 1
'Get holiday range
With Sheets("Holidays")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set HolidayRange = .Range("A2:A" & LastRow)
End With
MyYearStr = InputBox("Enter Year : ")
MyYear = Val(MyYearStr)
'Delete All sheets except Holiday
Application.DisplayAlerts = False
For ShtCount = Sheets.Count To 1 Step -1
If Sheets(ShtCount).Name < "Holidays" Then
Sheets(ShtCount).Delete
End If
Next ShtCount
Application.DisplayAlerts = True
For MonthCount = 1 To 12
'start calendar on column 2
ColCount = 2
'Get LastDay of the Month as a date
'the last day of the month is the day before
'the 1st day of the next month
LastDay = DateSerial(MyYear, MonthCount + 1, 1) - 1
'put name of month on worksheet tab
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
With NewSht
.Name = MonthName(MonthCount, abbreviate:=True)
.Range("A3") = "Bill Date"
.Range("A4") = "Holiday"
.Range("A" & FirstShiftRow) = "First Shift Number Needed"
.Range("A" & SecondShiftRow) = "Second Shift Number Needed"
'put days of month on column Header
For DayCount = 1 To Day(LastDay)
MyDate = DateSerial(MyYear, MonthCount, DayCount)
.Cells(1, ColCount) = Day(MyDate)
.Cells(2, ColCount) = Format(MyDate, "DDD")
'check if Bill Date
Select Case Day(MyDate)
Case 5, 7, 11, 14, 17, 21, 25, Day(LastDay)
BillDate = True
Case Else
BillDate = False
End Select
.Cells(3, ColCount) = BillDate
'check if date is a holiday
Holiday = False
For Each MyHoliday In HolidayRange
If MyDate = MyHoliday Then
Holiday = True
.Cells(4, ColCount) = "Yes"
Exit For
End If
Next MyHoliday
If Weekday(MyDate, vbSunday) = vbSaturday Or _
Weekday(MyDate, vbSunday) = vbSunday Or _
Holiday = True Then
If BillDate = True Then
AM_Needed = 2
PM_Needed = 2
Else
AM_Needed = 2
PM_Needed = 1
End If
Else
If BillDate = True Then
AM_Needed = 6
PM_Needed = 3
Else
If Weekday(MyDate, vbSunday) = vbFriday Then
AM_Needed = 5
PM_Needed = 3
Else
AM_Needed = 6
PM_Needed = 2
End If
End If
End If
.Cells(FirstShiftRow, ColCount) = AM_Needed
.Cells(SecondShiftRow, ColCount) = PM_Needed
ColCount = ColCount + 1
Next DayCount
.Columns.AutoFit
End With
Next MonthCount
End Sub
--------------------
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=183222
Microsoft Office Help