How? to go to cell when opening file

  • Thread starter Thread starter dpj
  • Start date Start date
D

dpj

I have a column containing dates of the year. How can the file be opened
to go to the cell containing todays date?
 
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)
 
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.?
 
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)
 
Back
Top