Open workbook to weekly worksheet containing today's date?

[swirlbread]

Good morning,

I have created a workbook that will function as a weekly schedule - each worksheet represents a new week. I would like to make it so when the workbook opens that it scans all of the worksheets and opens to the worksheet containing today's date. I have tried several different VBA codes and have been unsuccessful.

Some more information:
-Currently the worksheets are named as "Week of 8-2-21", "Week of 8-9-21", etc.
-Monday will be considered the first day of the week and each date is listed in cell B3 through H3 of each sheet. The remaining dates (starting in C3) are calculated using B3+1, etc. I have been just copying the previous worksheet and changing the date in B3.
-Dates in B3 are written in format mm/dd/yyyy but show up in the actual cell as Weekday, Month Day, Year

Thank you for any help you can provide! I am VERY new to macros and learning as I go, but this one has stumped me.

 

[Sequoyah]

Hi swirlbread,
insert this code in ThisWorkbook module - not a standard module

Private Sub Workbook_Open()
    
    Dim ws          As Worksheet
    Dim MyDate      As String
    
    For Each ws In ThisWorkbook.Sheets
        MyDate = Right(ws.Name, Len(ws.Name) - 8)
        
        If Format(MyDate, "ww-yyyy") = Format(Date, "ww-yyyy") Then
            ws.Activate
            Range("A1").Select
        End If
        
    Next ws
    
End Sub

 

[swirlbread]

Hi swirlbread,
insert this code in ThisWorkbook module - not a standard module

Private Sub Workbook_Open()
   
    Dim ws          As Worksheet
    Dim MyDate      As String
   
    For Each ws In ThisWorkbook.Sheets
        MyDate = Right(ws.Name, Len(ws.Name) - 8)
       
        If Format(MyDate, "ww-yyyy") = Format(Date, "ww-yyyy") Then
            ws.Activate
            Range("A1").Select
        End If
       
    Next ws
   
End Sub

Thank you so much! This worked perfectly. I wasn't anywhere close when I tried. Appreciate it very much.

 

[Sequoyah]

Hi, glad to help & thanks for the feedback.