Open workbook on todays date

[jshaw82]

HI,

I have a spreadsheet that I'm working on and I want it to select the cell that corresponds to today's date when you open the workbook

I already have the following that does a few other things when you open the workbook and need to incorporate it into this code:

The sheet "Rota" is the sheet with the list of dates on: I have attached an example for ease as once I've got the code I can figure out how to incorporate it.
dates test.xlsx

Please Login or Register  to view this content.

[HaHoBe]

Hi, jshaw82,

due to the file format itīs impossible to integrate macro code into an *.xlsx, you must either save as *.xlsm or *.xlsb and then copy the code into ThisWorkbook.

Your attachment shows an empty workbook - what shall be integrated where?

Ciao,
Holger

[jshaw82]

Sorry - see the attached - i hadn't saved it as a XLS

dates test.xls

[HaHoBe]

Hi, jshaw82,

sheet shows last year so maybe use this code in Workbook_Open:

Please Login or Register  to view this content.

Ciao,
Holger

[jshaw82]

Hi,

I cannot get the code to work - i think its because i have the cells that the date is in merged? This is on purpose - I have uploaded the test file with the code in so you can see. I have also formatted the sheet how it is in the actual file i'm working with.

dates test2.xls


Thanks

John

[HaHoBe]

Hi, John,

the cells that the date is in merged

No, itīs simply that no match can be found if you search for a date in January 2012 and the dates in the sheet start with June 2012.

Please Login or Register  to view this content.

Ciao,
Holger

[jshaw82]

Hi, I've added the code and today's date (28/01/2012) is in cell B396. Its not doing anything. Can you have a look at the attached file and see what I'm doing wrong?

dates test3.xls

Many thanks,
John

[HaHoBe]

Hi, John,

because the other workbooks showed last year dates I used this line to reflect that:

Please Login or Register  to view this content.

If the dates are from 2013 the code may be simplified to

Please Login or Register  to view this content.

and row 396 is on top of the window (at least for today).

HTH,
Holger

[jshaw82]

Thanks for you support on this - much appreciated!