How? to go to cell when opening file
 

I have a column containing dates of the year. How can the file be opened
to go to the cell containing todays date?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How? to go to cell when opening file
 

Here's some code that goes into the ThisWorkbook code module

Private Sub Workbook_Open()
Dim oFoundCell As Range

With ActiveSheet.Range("A1:A100")
Set oFoundCell = .Find(what:=Date, _
LookIn:=xlFormulas)
If Not oFoundCell Is Nothing Then
oFoundCell.Activate
End If
End With

End Sub


Change the range to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dpj" wrote in message
...
I have a column containing dates of the year. How can the file be opened
to go to the cell containing todays date?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How? to go to cell when opening file
 

Thank you for that Bob.
I had a bit of trouble to start with but after changing line:-
Set oFoundCell = .Find(what:=Date, LookIn:=xlFormulas)
to
Set oFoundCell = .Find(Date, LookIn:=xlFormulas) I found it worked.

Can the code be modified to select the correct sheet if there are more
than one, eg. 2003,2004,2005 etc.?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How? to go to cell when opening file
 

This should do it

Private Sub Workbook_Open()
Dim oFoundCell As Range
Dim sYear As String

sYear = CStr(Year(Date))
Worksheets(sYear).Activate
With ActiveSheet.Range("A1:A100")
Set oFoundCell = .Find(what:=Date, _
LookIn:=xlFormulas)
If Not oFoundCell Is Nothing Then
oFoundCell.Activate
End If
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dpj" wrote in message
...
Thank you for that Bob.
I had a bit of trouble to start with but after changing line:-
Set oFoundCell = .Find(what:=Date, LookIn:=xlFormulas)
to
Set oFoundCell = .Find(Date, LookIn:=xlFormulas) I found it worked.

Can the code be modified to select the correct sheet if there are more
than one, eg. 2003,2004,2005 etc.?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/