Activate today's sheet

[Martin_H]

Hi,

I have workbook with 5 worksheets in it. Sheets are named Monday, Tuesday, Wednesday, Thursday and Friday.

I want Excel to activate today's sheet on Workbook open procedure.

So basicaly if it's Monday today, Excel will activate sheet called Monday. If it's Tuesday today, Excel will activate sheet called Tuesday.

If it's Saturday or Sunday, then activate Monday.

Thank you for help. Much appreciated.

 

[GraH]

Maybe this very basic code is doing the job

Private Sub Workbook_Open()
i = Weekday(Date, vbUseSystemDayOfWeek)
Sheets(i).Select
End Sub

 

[Martin_H]

Maybe this very basic code is doing the job

Private Sub Workbook_Open()
i = Weekday(Date, vbUseSystemDayOfWeek)
Sheets(i).Select
End Sub

Hi GraH, thank you for your reply.

I used this instead:

Private Sub Workbook_Open()
Select Case Weekday(Date, vbMonday)
Case 1
Worksheets("Monday").Select
Case 2
Worksheets("Tuesday").Select
Case 3
Worksheets("Wednesday").Select
Case 4
Worksheets("Thursday").Select
Case 5
Worksheets("Friday").Select
Case 6
Worksheets("Monday").Select
Case 7
Worksheets("Monday").Select
End Select
End Sub

 

[Fluff]

You can slim that down slightly like

Private Sub Workbook_Open()
Select Case Weekday(Date, vbMonday)
Case 1, 6, 7
Worksheets("Monday").Select
Case 2
Worksheets("Tuesday").Select
Case 3
Worksheets("Wednesday").Select
Case 4
Worksheets("Thursday").Select
Case 5
Worksheets("Friday").Select
End Select
End Sub

 

[Peter_SSs]

I have workbook with 5 worksheets in it. Sheets are named Monday, Tuesday, Wednesday, Thursday and Friday.

Assuming the 5 sheets are in that order, try

Private Sub Workbook_Open()
  Sheets(Application.Max(Weekday(Date, vbSaturday) - 2, 1)).Activate
End Sub