Open specific sheet according to current Date

Question

I have 52 sheets in an excel workbook, and each one is named according to month-date in the format: 11-3, 11-10, 11-17, 11-24. The day corresponds with the Monday of the week.

My goal is to have code that will open the workbook to a sheet according to the date. So for example, if I opened it today (Nov 7) then it would open to sheet 11-3. If I opened it next week on Nov 7 it would open the 11-10 sheet. I've tried several different methods that don't work. If anyone has a suggestion it would be greatly appreciated.

Thanks

Answer 1

Something like this might work in the ThisWorkbook module's, Workbook_Open event.

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim mnth As String, dte As String, mday As String

mday = Now() - Weekday(Now(), 3)

mnth = Month(mday)
dte = Day(mday)

tabstr = mnth & "-" & dte

    For Each ws In Worksheets
        If ws.Name = tabstr Then
            ws.Select
            Exit For
        End If
    Next
End Sub