How? to go to cell when opening file

[dpj]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)