Macro to goto specific cell (todays date)

[crimekilla]

i have a sheet set up as follows

A B C
1 John Andrew Steve

2 01-01-2010

3 01-02-2010

4 01-03-2010

5 01-04-2010

this sheet goes up to the end of the year 12-31-2010
what i am looking for is a macro that when upon opening the document brings
the user to the row corisponding to the current days date.

 

[Roger Govier]

Hi

One way

Sub Auto_Open()
Sheets("Sheet1").Activate
Rows(Date - DateSerial(2010, 1, 1) + 2).Activate
End Sub

 

[Gord Dibben]

And if you want that row scrolled up, add the line

ActiveWindow.ScrollRow = ActiveCell.Row

before End Sub


Gord Dibben MS Excel MVP

 

[JLatham]

The code below must go into the Workbook's event code module. To put it
there, open the workbook and press [Alt]+[F11] to open the VB Editor. Press
[Ctrl]+[R] to make sure the "Project - VBAProject" pane is displayed. Expand
the list of objects in VBAProject for your workbook and double-click on the
"ThisWorkbook" entry in the list.
Copy the code below and paste it into that module. Edit the sheet name to
correspond with the correct sheet in your workbook. Close the VB editor.
Save the workbook.
To test it, pick another sheet in the workbook and save/close it. Open it
back up and it should go to the row with today's date in it.

Private Sub Workbook_Open()
'first make sure the correct
'sheet is active and ready to be used
'this sheet must be visible
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Activate
ActiveSheet.Cells.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

End Sub

 

[JLatham]

Posting the code again so that you may have a better chance of copying and
pasting it without errors getting injected because of this forum breaking
lines in the wrong places:

Private Sub Workbook_Open()
'first make sure the correct
'sheet is active and ready to be used
'this sheet must be visible
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Activate
ActiveSheet.Cells.Find(What:=Date, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub