roster date
hi, i have attached a spreadsheet that i originally recorded a macro for that calculates a roster for the group that i work with - it basically went backwards/forwards weeks. An acquaintance then "enhanced" it for me to quickly scroll back to the current week (ThisWeek VBA). I am now trying to use the VBA in this last change to have the roster go to a specified date (for example - so people can see what shift they are doing at easter, xmas etc)- have called this "ToWeek" but at the moment it is just a copy of the other VBA. My excel knowledge is pretty basic and i have tried altering the "ThisWeek" VBA but to no avail. Hope someone can point me in the right direction.
Happy to provide further information if needed,
thnx
| Attachment | Size |
|---|---|
| roster anon1.xlsm | 26.74 KB |
roster date
Was looking at my post and thought it sounded a bit confusing. Basically what i need is for the "ThisWeek" VBA to be altered to run the forward/backward macro for a specified date (i.e. value in cell m14). Hope this adds some clarification to my question.
thnx
oh, and here's the VBA code for "ThisWeek":
Sub ThisWeek()
Dim myRosterStartDate As Date
Dim myToday As Date
Dim DeltaDays As Double
'Get today's date
'Get the roster start date
myToday = Date
myRosterStartDate = Range("E3")
'Get the number of days between the roster start date and today
DeltaDays = myRosterStartDate - myToday
'Move backward until this week is displayed
While DeltaDays > 0
Call backward
myRosterStartDate = Range("E3")
DeltaDays = myRosterStartDate - myToday
Wend
'Move forward until this week is displayed
While DeltaDays < -6
Call forward
myRosterStartDate = Range("E3")
DeltaDays = myRosterStartDate - myToday
Wend
End Sub