Open worksheet based on this week's Monday date

[bujubenji]

Hi all,

I've put together a management information workbook which has a master sheet called 'Performance.' All of the remaining sheets are titled as the Monday of each week with a format ('dd.mm.yy'), i.e. today is 17.05.23, so this week's sheet is called '15.05.23.'

I'm looking for code for a workbook open event, which will activate this week's sheet to save the hassle of selecting it from a drop down or by any other means.

Does anybody have a viable solution please? I've searched all of the forums I can find and haven't had any luck.

Thanks in advance,

Buju

 

[Herakles]

Does this work for you?

You have a period, '.', after the 15.05.23, did you mean this to be there?

Private Sub Workbook_Open()
Dim Ws As Worksheet
    
    For Each Ws In ActiveWorkbook.Worksheets
        If Ws.Name = Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & "." Then
            Ws.Activate
            Exit For
        End If
    Next Ws

End Sub

 

[bujubenji]

Does this work for you?

You have a period, '.', after the 15.05.23, did you mean this to be there?

Private Sub Workbook_Open()
Dim Ws As Worksheet
   
    For Each Ws In ActiveWorkbook.Worksheets
        If Ws.Name = Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & "." Then
            Ws.Activate
            Exit For
        End If
    Next Ws

End Sub

Good morning Herakles,

Thanks so much for the above. It doesn't seem to work in this specific workbook- no error message at all, it just doesn't bring me to another worksheet.

I've just created a test document to trial it and it works just fine there. I suppose it's now just a case of tracking down which area of code might be interfering with this in my workbook?

Thanks so much for the assistance!

 

[Herakles]

Does tha Workbook_Open event code actually run?

Do you already have a Workbook_Open procedure with other code in it?

Try this, it will give you a message if the sheet it is looking for is not there.

Private Sub Workbook_Open()
Dim Ws As Worksheet
    
    For Each Ws In ActiveWorkbook.Worksheets
        If Ws.Name = Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & "." Then
            Ws.Activate
            Exit Sub
        End If
    Next Ws

    MsgBox Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & "." & " worksheet not found" & _
        " in this workbook.", vbInformation, "Warning"

End Sub

 

[bujubenji]

Does tha Workbook_Open event code actually run?

Do you already have a Workbook_Open procedure with other code in it?

Try this, it will give you a message if the sheet it is looking for is not there.

Private Sub Workbook_Open()
Dim Ws As Worksheet
   
    For Each Ws In ActiveWorkbook.Worksheets
        If Ws.Name = Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & "." Then
            Ws.Activate
            Exit Sub
        End If
    Next Ws

    MsgBox Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & "." & " worksheet not found" & _
        " in this workbook.", vbInformation, "Warning"

End Sub

You are a godsend! Thanks so much for this mate- it works now I just had to remove the & "." that I had erroneously typed in my initial question. Thanks again for the assistance, it is greatly appreciated.

 

[Herakles]

You are a godsend! Thanks so much for this mate- it works now I just had to remove the & "." that I had erroneously typed in my initial question. Thanks again for the assistance, it is greatly appreciated.

I did wonder about the "." and I did mention it.

You may want to delete this line as it would annoy me.

MsgBox Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & "." & " worksheet not found" & _
        " in this workbook.", vbInformation, "Warning"