Open workbook on today's date

obrijo

New Member
Joined
Jan 16, 2013
Messages
19
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this in the ThisWorkbook module

Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Possibly another option again to go in the ThisWorkbook module.

Code:
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

New Member
Joined
Jan 16, 2013
Messages
19
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,959
Messages
5,525,899
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top