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]

Replace your workbook_open module with this one:

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
End Sub

 

[drivingforce]

Replace your workbook_open module with this one:

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
End Sub

Thanks Joe!

Sorry but VBA is not my forte. At all.
But I feel like the code you shared (which I'm sure works) will need to be tweaked in some way so that it refers to the range where my dates are. They're all on row three between cells C3:NC3.

Cheers

 

[drivingforce]

What I mean is that I copied the code and the "Can't find today's date" popped up.

And the code I wrote down at the top was not my own but copied from another thread, so be aware... I know nothing about VBA

Thanks again.

 

[JoeMo]

What I mean is that I copied the code and the "Can't find today's date" popped up.

And the code I wrote down at the top was not my own but copied from another thread, so be aware... I know nothing about VBA

Thanks again.

How did you install the code I posted - as a standard module or in Thisworkbook? When your workbook opens is the sheet with the dates on it the active sheet? And, NO the code will not need tweaking - it will search for today's date on the entire active sheet.

 

[drivingforce]

How did you install the code I posted - as a standard module or in Thisworkbook? When your workbook opens is the sheet with the dates on it the active sheet? And, NO the code will not need tweaking - it will search for today's date on the entire active sheet.

Sorry i didn't mean to question your code, as you are clearly the superior Excel wizard here

After looking closer at my sheet I found the problem... My "date cells" do not actually contain the dates but it contains a formula calculating the date - In cell C3 I have 1/1/2014 and the subsequent cells have C3+1, D3+1, E3+1 etc...

After copying the cell values and re-pasting them, instead of the formulas it works fine!

Thank you so much for your assistance! Once again this forum has proved very helpful!

Cheers!

 

[JoeMo]

My "date cells" do not actually contain the dates but it contains a formula calculating the date - In cell C3 I have 1/1/2014 and the subsequent cells have C3+1, D3+1, E3+1 etc...

If you would like to retain the formulas in those cells then change this:

Set R = Cells.Find(Date, [A1], xlFormulas, xlWhole)

to this:

Set R = Cells.Find(Date, [A1], xlValues, xlWhole)</pre>

 

[drivingforce]

Hi Joe

First of all. Thanks for solving the problem I posted back in February. However... I might have encountered another issue where it seems two parts of my VBA are conflicting each other. This is my problem:

I have this code in Module2:

Sub Reset()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = Now + TimeValue("00:10:00") ' 10 minutes
Application.OnTime SchedSave, "SaveWork", , True
End Sub

which makes my spreadsheet auto save & close after 10 minutes of inactivity. This is an important feature as we are numerous staff member accessing this spreadsheet on different computer throughout any workday.


This all works find and dandy... Until I add the code you gave me:

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
End Sub

As soon as the Find code above is saved in ThisWorkbook my auto save/close feature stops working. As I do not know enough about VBA to figure out what is wrong. If there's a simple solution for this I'd be happy to hear about it, but if not, I'll just get rid of the Find code and call it a day

Thanks again.
Cheers!

 

[JoeMo]

Hi Joe

First of all. Thanks for solving the problem I posted back in February. However... I might have encountered another issue where it seems two parts of my VBA are conflicting each other. This is my problem:

I have this code in Module2:

Sub Reset()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = Now + TimeValue("00:10:00") ' 10 minutes
Application.OnTime SchedSave, "SaveWork", , True
End Sub

which makes my spreadsheet auto save & close after 10 minutes of inactivity. This is an important feature as we are numerous staff member accessing this spreadsheet on different computer throughout any workday.


This all works find and dandy... Until I add the code you gave me:

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
End Sub

As soon as the Find code above is saved in ThisWorkbook my auto save/close feature stops working. As I do not know enough about VBA to figure out what is wrong. If there's a simple solution for this I'd be happy to hear about it, but if not, I'll just get rid of the Find code and call it a day

Thanks again.
Cheers!

From what you have posted, I see no reason why the workbook_open event code should cause a problem, but it may be that you need to trigger the OnTime method when you first open the workbook. Can you post the SaveWork code?

 

[drivingforce]

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.