Sheet display in function of date and time on open and save Here's a simple one for u guys,
The code is called upon workbook_open and BeforeSave, it displays the right sheet in function of date and time (Day vs night shift vs date). The code run trough the sheet and activate it if the date on sheet is the same as the actual date. Since there's 2 sheets with the same date (day and night shift) i had to add a time condition. The nights shifts are before the day one (look at image) so i had to split my loop in 2 to make it work (could maybe be one). Also, if we're not in the week of that report, so that no sheets has a date that fits, i wanted it to select Friday cause it has a report on it. Since its called often i wanted to make sure that it is optimal.

Code :
Sub selectiondequartauto()
' Code that display sheet in fucntion of date and time

    Dim count As Long        ' Counter 
    Dim xSh As Worksheet
    Dim activation as Boolean 
    Dim sheetdate As Date

    Dim limitpm As Variant
    Dim limitam As Variant
    limitpm = TimeValue(&quot;16:15:00&quot;)  ' Night shift Start
    limitam = TimeValue(&quot;3:00:00&quot;)   ' Night shift end

    For Each xSh In Worksheets                   ' Loop on every visible sheet
        If xSh.Visible Then
           sheetdate = xSh.Cells(4, 2).Value ' Gives sheetdate the date of the sheet in present loop

            If TimeValue(Now) &lt; limitpm And TimeValue(Now) &gt; limitam Then

                If count &lt; 1 Then                ' If count is smaller than 1 (no sheet w same date as now yet)

                    If sheetdate = Date Then     ' Si Sheetdate = actual date 
                        count = count + 1        ' Count = 1 and next sheet, because 2 sheet have sheetdate = actual date
                    End If                       
                ElseIf count = 1 Then            ' Si count = 1
                    Activation = True
                    xSh.Activate                 ' Activate curent loop sheet and quit
                    Exit For
                End If

            Else  ' If we are in night shift time frame

                If sheetdate = Date Then
                    Activation = True
                    xSh.Activate                ' Activate curent loop sheet and quit
                    Exit For
                End If
            End If
        End If
    
    Next
    If Activation = False Then 
        Sheets(&quot;Vendredi jour&quot;).Activate
    End If
End Sub
selectiondequartauto() defines night shift to determine whether to select either the night shift or day shift sheet.  If none of the worksheets meet the criteria of Night Shift or Day Shift then the Friday worksheet is selected.  This all works fine but consider the advantages of my refactored code which separates these tasks into their own methods.
• isNightShift: Using a function that determines what is Night Shift and what is Day Shift means that you can update the hours of Night Shift in one spot and not have to look over every procedure that is dependent on on the Night Shift.
• NightShiftSheet() and DayShiftSheet():  Having these functions will prevent you from having to repeat the logic to find the sheets in the future.  They also make  selectiondequartauto() easier to read
Public Sub selectiondequartauto()
    Dim ws As Worksheet
    
    If isNightShift(Now) Then
        Set ws = NightShiftSheet
    Else
        Set ws = DayShiftSheet
    End If
    
    If ws Is Nothing Then
        Sheets(&quot;Vendredi jour&quot;).Activate
    Else
        ws.Activate
    End If
End Sub

Public Function DayShiftSheet() As Worksheet
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        With ws.Range(&quot;B4&quot;)
            If IsDate(.Value) Then
                If Not isNightShift(.Value) Then
                    Set DayShiftSheet = ws
                    Exit Function
                End If
            End If
        End With
    Next
    
End Function

Public Function NightShiftSheet() As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws.Range(&quot;B4&quot;)
            If IsDate(.Value) Then
                If isNightShift(.Value) Then
                    Set NightShiftSheet = ws
                    Exit Function
                End If
            End If
        End With
    Next
End Function

Public Function isNightShift(DateTime As Date) As Boolean
    isNightShift = TimeValue(DateTime) &gt; TimeValue(&quot;3:00:00&quot;) And TimeValue(DateTime) &lt; TimeValue(&quot;16:15:00&quot;)
End Function