Open Certain Worksheet Based on Current Date

[default_name]

Hello everyone!

Back in 2018 you guys helped me with some VBA code that would set the color of a certain tab in a timekeeping/pay period workbook depending on the date.
(see Tab Color Dependent on Date)
With a few tweaks over the years, the code used in the solution has worked flawlessly over the years! (thank you!)

However, now, I am attempting to add on to the code, but I am hesitant because I do not want to break it.
In addition to identifying a sheet based on the current date and changing the tab color, I would like for the workbook to open on the identified sheet when the file is opened.

Dates for each day in a pay period are displayed in the following cells of each worksheet: F1:S1
Dates are only listed once in the workbook.

Here is the code that I currently have:

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Fnd As Range
   For Each Ws In Worksheets
      Set Fnd = Ws.Range("F1:S1").Find(Date, , , xlWhole, , , , , False)
      Ws.Tab.Color = IIf(Fnd Is Nothing, False, vbBlack)
   Next Ws
End Sub

Would it be as simple as adding a line of code that opens the tab containing the result of the Fnd operation?

Thanks in advance!
I appreciate all of your help and support over the years!

 

[NdNoviceHlp]

You can trial this...

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Fnd As Range
   For Each Ws In Worksheets
      Set Fnd = Ws.Range("F1:S1").Find(Date, , , xlWhole, , , , , False)
      Ws.Tab.Color = IIf(Fnd Is Nothing, False, vbBlack)
      Ws.Select
      Ws.Activate
      Exit For
   Next Ws
End Sub

HTH. Dave

 

[default_name]

You can trial this...

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Fnd As Range
   For Each Ws In Worksheets
      Set Fnd = Ws.Range("F1:S1").Find(Date, , , xlWhole, , , , , False)
      Ws.Tab.Color = IIf(Fnd Is Nothing, False, vbBlack)
      Ws.Select
      Ws.Activate
      Exit For
   Next Ws
End Sub

HTH. Dave

Thanks for the quick response, NdNoviceHlp!
I gave your code a try but it didnt seem to work.
The original portion of the code still works though.

 

[NdNoviceHlp]

Maybe remove the "Exit For" as I see that your code actually intends to change all of the tab colours. Dave

 

[default_name]

Maybe remove the "Exit For" as I see that your code actually intends to change all of the tab colours. Dave

Removing "Exit For" automatically opens the workbook up at the very last sheet (the sheet furthest to the right).
Hmm, getting closer

 

[default_name]

If it helps any, each of the worksheets follow the same naming convention.
Here is an example:

Today's date is 01/15/2024
That date is found in the tab/sheet titled "0113-0126" (because that sheet contains the dates that span the pay period of Jan 13 to Jan 26)

I do see an example online that supposedly opens on a workbook based on date, but I am not quite sure how to get this to coincide with the existing 'tab color' code.

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

My attempt:

Private Sub Workbook_Open()
Dim Ws As Worksheet
Dim Fnd As Range
Dim mnth As String, dte As String, mday As String

mday = Now() - Weekday(Now(), 7)   'because pay periods start on Saturdays [7, right?]
mnth = Month(mday)
dte = Day(mday)
tabstr = mnth & dte &"-" & mnth & dte

  For Each Ws In Worksheets
     Set Fnd = Ws.Range("F1:S1").Find(Date, , , xlWhole, , , , , False)
         Ws.Tab.Color = IIf(Fnd Is Nothing, False, vbBlack)
         Ws.Select
         Ws.Activate
  Exit For
Next Ws
End Sub

 

[NdNoviceHlp]

The format for Ws.Range("F1:S1") in each sheet ... is that date format? I did some testing and as long as some sheet contains today's date then this will work however it seems to me that you actually want to find some date not today's date? Dave

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Fnd As Range
   For Each Ws In Worksheets
      With Ws
      Set Fnd = .Range("F1:S1").Find(Date, , , xlWhole, , , , , False)
      If Not Fnd Is Nothing Then
      .Tab.Color = vbBlack
      .Select
      '.Activate
      'Else
      '.Tab.Color = vbWhite
      End If
    End With
   Next Ws
End Sub

 

[default_name]

The format for Ws.Range("F1:S1") in each sheet ... is that date format? I did some testing and as long as some sheet contains today's date then this will work however it seems to me that you actually want to find some date not today's date? Dave

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Fnd As Range
   For Each Ws In Worksheets
      With Ws
      Set Fnd = .Range("F1:S1").Find(Date, , , xlWhole, , , , , False)
      If Not Fnd Is Nothing Then
      .Tab.Color = vbBlack
      .Select
      '.Activate
      'Else
      '.Tab.Color = vbWhite
      End If
    End With
   Next Ws
End Sub

Your code works perfectly!
Thank you for your help!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave