Macro to jump to today's date

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

Victor Delta

Hi

I have an Excel spreadsheet which includes, in Col A, a list of all 365 days
of the year.

When opening the spreadsheet, using an auto_open macro, I would like the
opening view to include today's date (perhaps a few rows down from the top).

Can anyone please recommend which functions I need to use in the macro to
make the spreadsheet open in this way.

Many thanks,

V
 
You could try

Columns(1).find(Date,,xlvalues,xlwhole).select

I've tried this sort of thing before and you can get problems becaus
of date formats
 
You can try to run this Victor


Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Sheet1").Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
 
Try:

With Range("A:A")
Set c = .Find(DateValue(Now()), LookIn:=xlValues)
If Not c Is Nothing Then
ActiveWindow.ScrollRow = c.Row - 3
End If
End With

this will position the dates 3 rows fom today.

HTH
 
Sub Macro1()
Dim lRow As Long
lRow = Date - DateSerial(Year(Date), 1, 1) + 1
ActiveWindow.ScrollRow = lRow + 1
Cells(lRow, "A").Activate
End Sub

HTH
 
Why not just put this in the ThisWorkbook module?:
Private Sub Workbook_Open()
ActiveSheet.Columns(1).Find(Date).Select
End Sub
 
Ron de Bruin said:
You can try to run this Victor
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Sheet1").Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Click to expand...

Ron

Many thanks - it works.

However, what do I need to add to this to scroll up about 3 lines - i.e. so
today's date is not right at the top? I've tried several VBA commands but
they don't seem to work for me!

Thanks,

V
 
Back
Top