Go to Date on multiple sheets?
 

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



--

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


"Pat Hawkins" wrote in message ...

Previous Posts In This Thread:

On Monday, January 14, 2008 2:06 PM
Pat Hawkins wrote:

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 advance
Pat

On Monday, January 14, 2008 2:19 PM
Ron de Bruin wrote:

Try this macroIf you have date's in column A then this example will select the
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



--

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


"Pat Hawkins" wrote in message ...

On Monday, January 14, 2008 3:50 PM
Pat Hawkins wrote:

RonThanks for your quick response. One more question.
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

"Ron de Bruin" wrote in message
...

On Monday, January 14, 2008 4:02 PM
Ron de Bruin wrote:

Hi PatWith the headers in row 1 of "Sheet1" try this oneSub Find_Date_test()
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


--

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


"Pat Hawkins" wrote in message ...

On Monday, January 14, 2008 6:08 PM
Pat Hawkins wrote:

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/tutorials...on-effect.aspx

Go to Date on multiple sheets?
 

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


On Tue, 27 Apr 2010 16:40:28 -0700, James Uzy wrote:

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



--

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


"Pat Hawkins" wrote in message ...

Previous Posts In This Thread:

On Monday, January 14, 2008 2:06 PM
Pat Hawkins wrote:

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 advance
Pat

On Monday, January 14, 2008 2:19 PM
Ron de Bruin wrote:

Try this macroIf you have date's in column A then this example will select the
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



--

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


"Pat Hawkins" wrote in message ...

On Monday, January 14, 2008 3:50 PM
Pat Hawkins wrote:

RonThanks for your quick response. One more question.
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

"Ron de Bruin" wrote in message
...

On Monday, January 14, 2008 4:02 PM
Ron de Bruin wrote:

Hi PatWith the headers in row 1 of "Sheet1" try this oneSub Find_Date_test()
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