how do i open a worksheet to "todays" date

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
113
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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

seguin85

Active Member
Joined
Mar 17, 2015
Messages
265
Office Version
  1. 365
Platform
  1. Windows
Code:
Private Sub Worksheet_Activate()    
Range("A1:A200").Find(Date).Select
End Sub
 

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
113
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

Active Member
Joined
Mar 17, 2015
Messages
265
Office Version
  1. 365
Platform
  1. Windows
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...

Code:
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

Board Regular
Joined
Dec 28, 2015
Messages
113

ADVERTISEMENT

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

Active Member
Joined
Mar 17, 2015
Messages
265
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 28, 2015
Messages
113
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

Board Regular
Joined
Dec 28, 2015
Messages
113
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,043
Messages
5,599,479
Members
414,312
Latest member
mikefire911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top