Auto start Macro to go to today's date in worksheet

  • Thread starter Thread starter Pat Hawkins
  • Start date Start date
P

Pat Hawkins

I need an Excel Macro that will select the current date from a column of
dates. This macro needs to run automatically when the worksheet is opened.
If anybody has any tips on how to do this, I would greatly appreciate it!

Thanks in advance
Pat
 
Try this macro

If you have date's in column A then this example will select the cell with today's date.

Sub Find_Todays_Date()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(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
 
Ron

Thanks for your quick response. One more question. In the spreadsheet I am
working on there are headings for each month. Each month is entered as
'mm/01/yyyy', but formatted to display as 'mmmm-yy'. As an example, the
January heading is entered as 01/01/2008 but is formatted as a custom date
to display as 'January-08'. How can I modify this macro, to go to the
heading for the current month?

Thanks again.

Pat
 
Hi Pat

With the headers in row 1 of "Sheet1" try this one

Sub Find_Date_test()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(DateSerial(Year(Date), Month(Date), 1))
With Sheets("Sheet1").Rows("1:1")
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
 
Thanks for all your help. That worked!

Pat

Ron de Bruin said:
Hi Pat

With the headers in row 1 of "Sheet1" try this one

Sub Find_Date_test()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(DateSerial(Year(Date), Month(Date), 1))
With Sheets("Sheet1").Rows("1:1")
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...
 
Hi and thanks for the post as this works witn a single sheet. I am trying to get excel to go to the date on multiple sheets when the workbook opens.
The code below allows this to happen but on one sheet only. Can someone please advise how to change this to include multiple sheets (there are only 4).
Very new to VBA and these forums have been gold. thanks




Ron de Bruin wrote:

Try this macroIf you have date's in column A then this example will select the
14-Jan-08

Try this macr

If you have date's in column A then this example will select the cell with today's date

Sub Find_Todays_Date(
Dim FindString As Dat
Dim Rng As Rang
FindString = CLng(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 The
Application.Goto Rng, Tru
Els
MsgBox "Nothing found
End I
End Wit
End Su


--

Regards Ron de Brui
http://www.rondebruin.nl/tips.ht


Previous Posts In This Thread:

Auto start Macro to go to today's date in worksheet
I need an Excel Macro that will select the current date from a column of
dates. This macro needs to run automatically when the worksheet is opened.
If anybody has any tips on how to do this, I would greatly appreciate it

Thanks in advanc
Pat

Try this macroIf you have date's in column A then this example will select the
Try this macr

If you have date's in column A then this example will select the cell with today's date

Sub Find_Todays_Date(
Dim FindString As Dat
Dim Rng As Rang
FindString = CLng(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 The
Application.Goto Rng, Tru
Els
MsgBox "Nothing found
End I
End Wit
End Su


--

Regards Ron de Brui
http://www.rondebruin.nl/tips.ht


RonThanks for your quick response. One more question.
Ro

Thanks for your quick response. One more question. In the spreadsheet I am
working on there are headings for each month. Each month is entered as
'mm/01/yyyy', but formatted to display as 'mmmm-yy'. As an example, the
January heading is entered as 01/01/2008 but is formatted as a custom date
to display as 'January-08'. How can I modify this macro, to go to the
heading for the current month

Thanks again

Pa


Hi PatWith the headers in row 1 of "Sheet1" try this oneSub Find_Date_test()
Hi Pa

With the headers in row 1 of "Sheet1" try this on

Sub Find_Date_test(
Dim FindString As Dat
Dim Rng As Rang
FindString = CLng(DateSerial(Year(Date), Month(Date), 1)
With Sheets("Sheet1").Rows("1:1"
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



Thanks for all your help. That worked!
Thanks for all your help. That worked!

Pat


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Reflection Effect
http://www.eggheadcafe.com/tutorial...-beab-49bd76e20b9b/wpf-reflection-effect.aspx
 
Sub Find_Todays_Date()
Dim FindString As Date
Dim Rng As Range
Dim ws As Worksheet
FindString = CLng(Date)
For Each ws In ActiveWorkbook.Worksheets
With ws.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 on " & ws.Name
End If

End With
Next
End Sub


Gord Dibben MS Excel MVP
 
This works perfectly if the date is keyed as an actual date. However, my dates are calculated based on the first date in the row (i.e., =NK5+7).

If I display the date using MsgBox, it looks like a date. If I compare Range("nu5") = CLng(Date) it returns True.
But when I run the macro, it displays "Nothing found."

I'm confused. Thanks for your help... MPV
 
This works perfectly if the date is keyed as an actual date. However, my dates are calculated based on the first date in the row (i.e., =NK5+7).

If I display the date using MsgBox, it looks like a date. If I compare Range("nu5") = CLng(Date) it returns True.
But when I run the macro, it displays "Nothing found."

I'm confused. Thanks for your help... MPV
 
Dates are 7 days apart.................(i.e., =NK5+7)

I would think today's date would be found only every 7th day?

Also............are your dates actually on "Sheet1"


Gord Dibben MS Excel MVP
 
Back
Top