Move to a specific sheet in the workbook based on the date

[sofas]

Welcome . I know that my request is somewhat strange, but I believe that nothing is impossible in this wonderful forum. I have several worksheets inside the workbook that are named today when they are first created. For example,21/1/2024. After a while, I want to retrieve some data from a specific sheet. What I am looking for is a way that enables me to select the appropriate sheet. When I enter a specific date, for example, in Sheet 1, cell B2, and pressing the button, I move directly to the sheet that is equal to the same date or smaller. Directly from it and ignore the rest

 

[sofas]

This code is to make the idea more clear. Until now, I am able to select the worksheet when the name matches, which date I am missing. When the same date does not exist, I move to the smallest date that follows the date of Sheet 1, cell b2.

Sub sheet_if()
    Dim mhName As String, ws As Worksheet, y As String
    
    On Error Resume Next
    mhName = Format(Sheets("Sheet1").Range("b2").Value, "dd-mm-yyyy")
    Application.ScreenUpdating = False
  
    y = Worksheets(mhName).Name
    
    If y <> "" Then
 
     Set ws = Worksheets(mhName)
     'Code
    
    Else
        MsgBox "attention  " & mhName & _
        "  Worksheet not found.", _
        vbInformation, "admin"
        
    End If
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Would this work for you?
Instead of pressing a button after entering a date in B2 of Sheet1, this code would take you to the relevant sheet (if one exists) as soon as you enter the date in B2.
This code would go in the Sheet1 code module.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ws As Worksheet
  Dim ShtDate As Date, MaxDate As Date

  If Not Intersect(Target, Range("B2")) Is Nothing Then
    If Len(Range("B2").Value) > 0 Then
      If IsDate(Range("B2").Value) Then
        For Each ws In Worksheets
          If IsDate(ws.Name) Then
            ShtDate = CDate(ws.Name)
            If ShtDate <= Range("B2").Value And ShtDate > MaxDate Then MaxDate = ShtDate
          End If
        Next ws
        If MaxDate = 0 Then
          MsgBox "No sheet found"
        Else
          Sheets(Format(MaxDate, "dd-mm-yyyy")).Activate
        End If
      Else
        MsgBox "You must enter a date"
      End If
    End If
  End If
End Sub

 

[sofas]

Would this work for you?
Instead of pressing a button after entering a date in B2 of Sheet1, this code would take you to the relevant sheet (if one exists) as soon as you enter the date in B2.
This code would go in the Sheet1 code module.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ws As Worksheet
  Dim ShtDate As Date, MaxDate As Date

  If Not Intersect(Target, Range("B2")) Is Nothing Then
    If Len(Range("B2").Value) > 0 Then
      If IsDate(Range("B2").Value) Then
        For Each ws In Worksheets
          If IsDate(ws.Name) Then
            ShtDate = CDate(ws.Name)
            If ShtDate <= Range("B2").Value And ShtDate > MaxDate Then MaxDate = ShtDate
          End If
        Next ws
        If MaxDate = 0 Then
          MsgBox "No sheet found"
        Else
          Sheets(Format(MaxDate, "dd-mm-yyyy")).Activate
        End If
      Else
        MsgBox "You must enter a date"
      End If
    End If
  End If
End Sub

Thank you it works fine. I will try to modify it to put it in a button because the idea was only to get the target sheet so that I can announce the name of the worksheet in another code to fetch the data.

 

[Peter_SSs]

Then remove that Worksheet_Change code, put this code in a standard module and have your button on Sheet1 run this code.

Sub GoToDateSheet()
  Dim ws As Worksheet
  Dim ShtDate As Date, MaxDate As Date

  If Len(Range("B2").Value) > 0 Then
    If IsDate(Range("B2").Value) Then
      For Each ws In Worksheets
        If IsDate(ws.Name) Then
          ShtDate = CDate(ws.Name)
          If ShtDate <= Range("B2").Value And ShtDate > MaxDate Then MaxDate = ShtDate
        End If
      Next ws
      If MaxDate = 0 Then
        MsgBox "No sheet found"
      Else
        Sheets(Format(MaxDate, "dd-mm-yyyy")).Activate
      End If
    Else
      MsgBox "You must enter a date"
    End If
  End If
End Sub