Option Explicit
Option Base 1
Sub transfer()
Dim DAILY As Worksheet, _
SCHEDULE As Worksheet, _
WkNum As Long, _
DestColumn As Long, _
DOW As Long, _
StartHour As Long, _
StartTime As String, _
ScheduleRow As Variant, _
ScheduleCol As Variant, _
Endtime As Variant, _
WeekTotals As Variant, _
DayTotal As Variant, _
RowTotal As Variant
Set DAILY = Sheets("Daily Duty Assignments")
Set SCHEDULE = Sheets("Schedule")
WkNum = DAILY.Range("N1").Value
Set ScheduleCol = SCHEDULE.Range("3:3").Find(WkNum, LookIn:=xlValues)
'get the column number
DestColumn = ScheduleCol.Column
' find the weeknumber on the schedule sheet and scroll it to the top left
If ActiveSheet.Name = SCHEDULE.Name Then
Range(ScheduleCol.Address).Activate
Application.Goto ActiveCell.Offset(3, 0), True
ActiveWindow.SmallScroll Up:=5
End If
'week totals are the columns at the end of each weekday table/range
WeekTotals = Array("I4:I15", "N20:N31", "N36:N47", "N52:N63", "N68:N79", "O84:O95", "O100:O111")
With DAILY
For Each DayTotal In WeekTotals
'set pointer to day of the current week (daily N1)
DestColumn = ScheduleCol + DOW
'check each cell in rowtotal
For Each RowTotal In .Range(DayTotal)
If RowTotal > 0 Then
'start at column C and move right while it is blank (not working)
For StartHour = 3 To RowTotal.Column - 1
If .Cells(RowTotal.Row, StartHour) <> "" Then
'when none blank cell found then get the employee's row
Set ScheduleRow = SCHEDULE.Range("B:B").Find(.Cells(RowTotal.Row, "B").Value, LookIn:=xlValues)
ScheduleRow = ScheduleRow.Row
'get the start time from row 2 of the current day table and remove the trailing " -"
StartTime = .Cells(.Range(DayTotal).Row - 1, StartHour).Value
StartTime = Left(StartTime, Len(StartTime) - 2)
'the end time is the sum of start time and total hours worked. If the sum is greater than
'12 then get the remainder (assumes p.m.)
'convert string value to integer, calculate and return to string
Endtime = (CLng(Left(.Cells(.Range(DayTotal).Row - 1, StartHour + RowTotal - 1).Value, 2)) + 1) Mod 12
Endtime = Endtime & Right(.Cells(.Range(DayTotal).Row - 1, StartHour + RowTotal - 1).Value, 7)
Endtime = Left(Endtime, Len(Endtime) - 2)
'write to schedule
SCHEDULE.Cells(ScheduleRow, DestColumn).Resize(columnsize:=2).Value = Array(StartTime, Endtime)
Exit For
End If
Next StartHour
End If 'rowtotal
Next RowTotal
'since there are two columns per day (start/end)
'increment the day pointer offset
'note that DOW
DOW = DOW + 2
Next DayTotal
End With 'daily
End Sub
Bookmarks