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

[Guest]

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)

 

[Mauro Gamberini]

=TODAY()

--

 

[Guest]

How do I enter this and where, I am a novice

=TODAY()

--

 

[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

 

[Guest]

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

 

[JE McGimpsey]

Changing

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

to

With Sheets("Sheet1").Cells


should work for you.

 

[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

 

[JE McGimpsey]

Much more reliable. Thanks for the correction...

 

[Guest]

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