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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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
14,460
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
14,460
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
14,460
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,939
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top