Finding Worksheet and Activating it
 

How would I go about searching for a specific worksheet and activating it
upon opening a workbook?

The worksheet should be named as Todays month and date, like this... 1-30.

This is what I have started...

Private Sub Workbook_Open()
Dim yDay, tDay, yMth, tMth
Dim dFri, dMon, dNow

yDay = Day(Date) - 1
tDay = Day(Date)
yMth = Month(Date) - 1
tMth = Month(Date)
dFri = 6
dMon = 2
dNow = Weekday(Date)


If Sheets(tMth & "-" & tDay).Name = True Then

Sheets(tMth & "-" & tDay).Activate
Exit Sub

Else
MsgBox "Please create a sheet for today", vbOKOnly

End If

End Sub

Any help would be awesome.

Thanks in advance,
Rob

Finding Worksheet and Activating it
 

One way:

Option Explicit
Private Sub Workbook_Open()

Dim myStr As String
Dim wks As Worksheet

myStr = Format(Date, "m-d")

Set wks = Nothing
On Error Resume Next
Set wks = Me.Worksheets(myStr)
On Error GoTo 0

If wks Is Nothing Then
MsgBox "Please create a sheet for today", vbOKOnly
Else
Application.Goto wks.Range("A1"), Scroll:=True
End If

End Sub

Just a suggestion...

You may want to create worksheets that are named in yyyy-mm-dd format. It could
make sorting easier and you won't have to worry about next year's worksheet
names.

Rob wrote:

How would I go about searching for a specific worksheet and activating it
upon opening a workbook?

The worksheet should be named as Todays month and date, like this... 1-30.

This is what I have started...

Private Sub Workbook_Open()
Dim yDay, tDay, yMth, tMth
Dim dFri, dMon, dNow

yDay = Day(Date) - 1
tDay = Day(Date)
yMth = Month(Date) - 1
tMth = Month(Date)
dFri = 6
dMon = 2
dNow = Weekday(Date)


If Sheets(tMth & "-" & tDay).Name = True Then

Sheets(tMth & "-" & tDay).Activate
Exit Sub

Else
MsgBox "Please create a sheet for today", vbOKOnly

End If

End Sub

Any help would be awesome.

Thanks in advance,
Rob

--

Dave Peterson

Finding Worksheet and Activating it
 

That Works Beautifully!!!

Thank You Sooo Very Much!
Rob


"Dave Peterson" wrote:

One way:

Option Explicit
Private Sub Workbook_Open()

Dim myStr As String
Dim wks As Worksheet

myStr = Format(Date, "m-d")

Set wks = Nothing
On Error Resume Next
Set wks = Me.Worksheets(myStr)
On Error GoTo 0

If wks Is Nothing Then
MsgBox "Please create a sheet for today", vbOKOnly
Else
Application.Goto wks.Range("A1"), Scroll:=True
End If

End Sub

Just a suggestion...

You may want to create worksheets that are named in yyyy-mm-dd format. It could
make sorting easier and you won't have to worry about next year's worksheet
names.

Rob wrote:

How would I go about searching for a specific worksheet and activating it
upon opening a workbook?

The worksheet should be named as Todays month and date, like this... 1-30.

This is what I have started...

Private Sub Workbook_Open()
Dim yDay, tDay, yMth, tMth
Dim dFri, dMon, dNow

yDay = Day(Date) - 1
tDay = Day(Date)
yMth = Month(Date) - 1
tMth = Month(Date)
dFri = 6
dMon = 2
dNow = Weekday(Date)


If Sheets(tMth & "-" & tDay).Name = True Then

Sheets(tMth & "-" & tDay).Activate
Exit Sub

Else
MsgBox "Please create a sheet for today", vbOKOnly

End If

End Sub

Any help would be awesome.

Thanks in advance,
Rob

--

Dave Peterson