Open workbook on today's date

[obrijo]

Hello

When I open a workbook & specific worksheet I want to go directly to the cell which contains today's date. The dates are formatted as custom & "dd-mmm". I checked it out online but I failed to get any of the code,macros etc to work.

Cheers

John

 

[VoG]

Try like this in the ThisWorkbook module

Private Sub Workbook_Open()
Dim Found As Range
Sheets("Sheet5").Select
Set Found = Columns("A").Find(what:=Date, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Select
End Sub

 

[MARK858]

Possibly another option again to go in the ThisWorkbook module.

Private Sub Workbook_Open()

    Dim sdate As String, cll As Range, sht As Worksheet
    Set sht = ActiveWorkbook.Sheets("Sheet1")
    sdate = Format(Date, "Short Date")

    On Error Resume Next
    Set cll = sht.Columns(1).Find(what:=CDate(sdate), After:=sht.Range("A1"), LookIn:=xlFormulas _
              , lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    On Error GoTo 0

    If Not cll Is Nothing Then
        Application.Goto reference:=cll
    End If

End Sub

 

[obrijo]

No luck. getting an error on the Set Sht code line. Changed the sheet to the worksheet I am using. Still getting an error.

John

 

[MARK858]

What does the error actually say and post the code you used (I'm not that good at guessing )

 

[VoG]

Did you try my (tested) code?

 

[MARK858]

VoG, I did test my code by saving the workbook to my desktop with the cursor in a different page and then reopening it with the cells formatted as per the OP's post. Is there something you can see that I missed?

 

[VoG]

Hi Mark. I didn't mean any criticism of your code. I just knew that mine worked when I tested it and my post was meant for the OP.