how do i open a worksheet to "todays" date

[Manexcel]

I have a worksheet that contains a date, format DDMMMYY, for each day of the year (there will be a minimum of two rows per any given date of the year). The dates are in column A and commence at cell A2 (row A1 is a header row).

How can I highlight and position the curser (also 'todays' date is at the top of the screen) to the cells that contains 'todays' date each time I open this worksheet?

Thank you for all your support.

 

[seguin85]

Private Sub Worksheet_Activate()    
Range("A1:A200").Find(Date).Select
End Sub

 

[Manexcel]

Thank you for the code. I have copied the code you provided to the spreadsheet where I want the date highlighted. I have changed the range to A1:a1000.

I ran the code via the sub/userform (f5) button and todays date is highlighted - irrespective of where the cursor is placed on the worksheet. I have saved the code and returned to excel. I closed the workbook and re opened expecting todays date to be highlighted but the curser / focus remains at the last cell before the workbook was saved and closed.

Can you please advise many thanks.

 

[seguin85]

I understood that you wanted today's date highlighted when you selected the sheet in mind. The code I wrote will select today's date when you switch from another sheet to the sheet in question. To select today's date when the file opens...

Private Sub Workbook_Open()
Worksheets("Sheet1").Activate
Range("A1:A1000").Find(Date).Select
End Sub

When your workbook opens, it will select "Sheet1" or whatever your sheet is called, then it will select today's date.

Hope this helps!

 

[Manexcel]

Thank you for responding to my query.

The first piece of code you provided now works as I go from sheet to sheet (my lack of understanding!) the focus goes onto todays date - all appears to work fine. Thank you.

However, as for the code that places the focus on todays date when the workbook is opened. I copied the code you supplied into the worksheet - changed the name of the worksheet in the code to the name of my worksheet - year_2016. Saved the workbook. And reopened the workbook but the focus did not go to todays date.

Can you please advise. (If it assists the date format is ddmmmyyy)

Thanks.

 

[seguin85]

My only thought would be that the code isn't in the right location. Ensure that the code is written in ThisWorkbook and not within the year_2016. Hope this solves your problem

 

[Manexcel]

By putting the code in ThisWorkbook has solved the problem. The date has focus when I open the workbook (code you provided on iteration 2). In addition, the date has focus when I move between worksheets.(code you provided on iteration 1).

Thank you very much for your help and patience. It was very much appreciated.

 

[Manexcel]

I am experiencing a small problem in that if I filter the data and the filtered data does not contain 'todays' date I get a 'run time error 91', 'object variable or with block variable not set'. Is there any error trapping / message that can be added to the code you have provided that if 'todays' date cannot be found a message is displayed, say for example "todays date not found - check data is not filtered" or whatever, and the routine ends without error message.

Thank you.