Open spreadsheet to a specific dynamic cell

[drivingforce]

Hi all knowledgeable excel people out there!

The code below will open my spreadsheet at any specified fixed location (e.g. A1) , but is it possible to re-write this code in such a way that the spreadsheet will open at a dynamic location instead?

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
Range("A1").Select
End Sub

What I have is a spreadsheet which contains dates for a couple of years on row 3, and I'd like the spreadsheet to open at today's location, if possible?

Thanks.

 

[JoeMo]

Sure thing. SaveWork code below.

Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Unless you see a simple solution to this, I am leaning towards just getting rid of the workbook_open code as this is just a convenience thing, and really not that important.

Thanks again.

I see no reason why the workbook_open code would shut down your autosave, but before you abandon it try this one change:

Private Sub Workbook_Open()
Dim R As Range
Set R = Cells.Find(Date, [A1], xlFormulas, xlWhole)
If Not R Is Nothing Then
ActiveWindow.ScrollRow = R.Row
R.Select
Else
MsgBox "Can't find today's date"
End If
Call Reset
End Sub

 

[drivingforce]

I see no reason why the workbook_open code would shut down your autosave, but before you abandon it try this one change:

Private Sub Workbook_Open()
Dim R As Range
Set R = Cells.Find(Date, [A1], xlFormulas, xlWhole)
If Not R Is Nothing Then
ActiveWindow.ScrollRow = R.Row
R.Select
Else
MsgBox "Can't find today's date"
End If
Call Reset
End Sub

Excellent! That worked out great.

Thank you SO much for all your help! My co-workers and I sure appreciate it!
Cheers!

 

[JoeMo]

Excellent! That worked out great.

Thank you SO much for all your help! My co-workers and I sure appreciate it!
Cheers!

You are welcome - thanks for the reply.