Open worksheet according to time and date

[wgibcus]

I have a workbook that teachers use to mark attendance for their class each day. There are two worksheets for each month - one for morning and one for afternoon (named Jan AM and Jan PM, Feb AM and Feb PM etc). The morning attendance sheet automatically updates the afternoon sheet with each student's status so that afternoon attendance is easier to mark (it is a legal requirement that the roll be marked twice a day). The problem is that some teachers forget to switch back to the morning worksheet the next day, because Excel remembers the last worksheet, and then they update the afternoon worksheet by mistake.

I would like to know how to use VB to calculate the month and the time (morning or afternoon) and have Excel open with the appropriate worksheet. I'm guessing it would be a variation of

Private Sub Workbook_Open()
Worksheets("Jan AM").Activate
End Sub

which currently opens the January morning attendance worksheet when Excel opens.

Any help is gratefully appreciated.

Waynos

 

[VoG]

Try

Worksheets(Format(Date, "mmm") & " " & IIf(Time > 0.5, "PM", "AM")).Activate

 

[wgibcus]

Perfect. Works like charm.

Many thanks Peter!