go to todays date in a sheet automatically when i open it

C

Cyril the Squirrel

I have a spread sheet with lots of dates, I want to go to todays cell
automatically when I open the workbook, does anyone know of a way to do this
easily as I am a novice with excel.
using Excel 2000 (9.0.4402 SR-1)
 
J

JE McGimpsey

And how exactly would one determine what "todays cell" is? Are there
dates in column A? Row 1? Randomly scattered throughout the sheet?

If you have sequential dates in column A of sheet1 of your workbook, you
could use something like:

Private Sub Workbook_Open()
Dim rFound As Range
Dim nDate As Long
nDate = Date + 1462 * ActiveWorkbook.Date1904
MsgBox nDate
With Sheets("Sheet1").Columns(1)
Set rFound = .Cells(Application.Match(nDate, .Cells))
If Not rFound Is Nothing Then _
Application.Goto rFound, Scroll:=True
End With
End Sub



If you're unfamiliar with macros, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
C

Cyril the Squirrel

thank you for your assistance, the sheet is made up of alternating columns
with a week number heading as shown below so columna a,c,e would contain
dates
although the sheet could be altered to accomodate.

29
10/10/04 NR
11/10/04 NA
12/10/04 Sick
13/10/04 NA
14/10/04 NA
15/10/04 NA
16/10/04 NR
 
J

JE McGimpsey

Changing

With Sheets("Sheet1").Columns(1)

to

With Sheets("Sheet1").Cells


should work for you.
 
D

Dave Peterson

I think you're going to look at each column. (Using .find can be miserable with
dates.)

I modified J.E.'s code and it worked ok for me:

Option Explicit
Private Sub Workbook_Open()

Dim res As Variant
Dim myCols As Variant
Dim nDate As Long
Dim iCtr As Long

myCols = Array("a", "c", "E")

nDate = Date + 1462 * ActiveWorkbook.Date1904
'MsgBox nDate
With Sheets("Sheet1")
For iCtr = LBound(myCols) To UBound(myCols)
With .Cells(1, myCols(iCtr)).EntireColumn
res = Application.Match(nDate, .Cells, 0)
If IsError(res) Then
'keep looking
Else
Application.Goto .Cells(res), Scroll:=True
Exit For
End If
End With
Next iCtr
End With
End Sub
 
C

Cyril the Squirrel

That worked fine.
Thank you to everyone concerned I am now A little wiser, confused buy none
the less wiser.
 
Top