Solved: open event code for a Work sheet with varible name

[BENSON]

I have a worksheet that when opened adds the date to the worksheets name see code:


Private Sub Workbook_Open()
ThisWorkbook.Worksheets(1).Name = "CHECK LIST " & Format(Date, "d mmm yy")


My problem is as the worksheets name will be different each day how should I alter the code below to take this in to account.I have mrked the line of code which gives me the problem in red.

Thanks


Public Sub NextCheck()

With Worksheets("CHECK LIST")
If Application.CountA(.Range("C6:E10")) <> .Range("C6:E10").Cells.Count Then

MsgBox "Data incomplete"
End If
End With

Select Case True

Case Time < TimeSerial(10, 5, 0)

mgNextRun = TimeSerial(10, 5, 0)
Case Time < TimeSerial(14, 5, 0)

mgNextRun = TimeSerial(14, 5, 0)
Case Time < TimeSerial(17, 5, 0)

mgNextRun = TimeSerial(17, 5, 0)
Case Else

mgNextRun = Date + 1 + TimeSerial(10, 5, 0)
End Select

Application.OnTime mgNextRun, "NextCheck"
End Sub

[Bartek]

Hi,

Use either:

With Worksheets("CHECK LIST " & Format(Date, "d mmm yy"))
(this may fail if you open the worksheet close before midnight)

or (in you don't alter the worksheet position):

With Worksheets(1)

[Bob Phillips]

What is the rule to determine which sheet is activated?

[BENSON]

THANKS FOR THE HELP