Force to open today’s date sheet while opening a workbook.

[kajee]

I have a workbook of “monthly employees schedule” for a month. Each sheet cel C2 has date like feb-16, feb-17’ feb-18, feb-19......
I want force it to open today’s sheet like today is feb-18 while opening workbook.

 

[Zot]

In ThisWorkbook (not normal macro module), put this code

Private Sub Workbook_Open()

Dim strShtName As String
Dim ws As Worksheet

strShtName = MonthName(Month(Date), True) & "-" & Day(Date)

For Each ws In Sheets
    If ws.Name = strShtName Then
        ws.Activate
    End If
Next

End Sub

 

[Peter_SSs]

Each sheet cel C2 has date like feb-16, feb-17’ feb-18, feb-19

Is the name of each sheet the same as the value in C2?
If not, how are the sheets named?

Also, are those dates in C2 actual dates (numbers) or text values?

 

[Zot]

Thanks @Peter_SSs. My mistake. I thought I read each sheet has tab named as Feb-16, Feb-17, etc ?

 

[Zot]

I have a workbook of “monthly employees schedule” for a month. Each sheet cel C2 has date like feb-16, feb-17’ feb-18, feb-19......
I want force it to open today’s sheet like today is feb-18 while opening workbook.

Can you please tell us if the C2 is date formatted or just a text entry?

 

[kajee]

Can you please tell us if the C2 is date formatted or just a text entry?

Date format

 

[Zot]

Date format

Just put the code below in ThisWorkbook as before

Once the workbook is opened, the sheet with same date will be activated or selected automatically

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In Sheets
    If ws.Range("C2") = Date Then
        ws.Activate
    End If
Next

End Sub

 

[Peter_SSs]

I thought I read each sheet has tab named as Feb-16, Feb-17, etc

I would still be interested to know about that.

Is the name of each sheet the same as the value in C2?
If not, how are the sheets named?

 

[kajee]

Just put the code below in ThisWorkbook as before

Once the workbook is opened, the sheet with same date will be activated or selected automatically

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In Sheets
    If ws.Range("C2") = Date Then
        ws.Activate
    End If
Next

End Sub

Sorry still didn’t work for me.

 

[Peter_SSs]

Sorry still didn’t work for me.

That gives helpers nothing to go on.
Did it ..
- give an error message? (what message and on what line of code?)
- open up and activate the wrong sheet? (What was the date when you ran it and what sheet did it activate)
- do nothing?
- something else?

You also didn't directly address my questions about the sheet names, however, I can see them in the image in which case you could try this Wokbook_Open event code instead.

Private Sub Workbook_Open()
  On Error Resume Next
  Sheets(Format(Date, "mmm dd")).Activate
End Sub

As Zot noted, this code must go in the ThisWorkbook module as shown below.