Spreadsheets: Open to Today’s Date

Reader David H. wins a copy of Office VBA Macros You Can Use Today for submitting this question: “I have a column which has each day of the month in it (e.g., 1, 2, 3, 4, etc.). This is what I’d like Excel to do: when I open the spreadsheet, I want to go to the cell that has today’s day of the month in it. For example, if today is October 1, 2011, I want Excel to go to the cell that has ‘1’ in it for the 1st of the month. I think it’s pretty simple, but I am just not sure what the ‘trick’ is to make it do what I need.”

Ah, yes, this sounds simple — and it only takes two lines to solve it — but any time you need the cell pointer to move to a specific place automatically, you are going to need a macro. Don’t worry, though: this macro is a short one and this article will walk you through adding it to your workbook.

Use a File Extension That Allows Macros

Back in the good old days, all Excel files were stored as .XLS files and they all allowed macros. Starting in Excel 2007, you had a choice of .XLSB, .XLSX, .XLSM, or .XLS. Out of those four file types, macros work fine in 75% of them. Unfortunately, the one file type that disallows macros was the default file type in Excel 2007/2010. If your file is currently stored as an .XLSX file, you need to use Save As and save the file as one of the other file types. After the Save As, close the file and reopen to enable the macro language.

Allowing Macros in Excel

By default, macros are turned off in Excel. Follow these steps one time to allow macros:
• In Excel 2003 or earlier, choose Tools, Macro, Security. Change the setting to Medium.
• In Excel 2007 or later, type Alt T followed by M then S. Choose “Disable All Macros With Notification.”

With both of these settings, you will receive a notification when you open a workbook with macros. If you are the author of the macros and know what they do, you can choose to enable them.

Find the Workbook Code Pane

Even if you’ve done macros before, this special one is an event macro that runs automatically when the workbook opens. Follow these steps to get to the right place for your macro:
1. Open your workbook.
2. Press Alt F11 to open the VBA Editor.
3. Press Ctrl R to display the Project Explorer. You will see a bold VBAProject entry for every open workbook or open Add-In (see Figure 1).

Fig. 1

MrExcel 3-16-11 Fig01

4. Click the Plus sign to the left of your workbook. You will see a folder called Microsoft Excel Objects.
5. Click the Plus sign to the left of Microsoft Excel Objects. You will see all of the worksheets in your workbook. At the bottom of the list is an entry called ThisWorkbook (Figure 2, below).
6. Double click on ThisWorkbook. A blank code module will appear on the right.
7. At the top of the code pane, there are two dropdowns. Open the left dropdown and choose Workbook. The right dropdown will automatically change to Open and the start of a Workbook_Open macro will appear in the code pane.

Fig. 2

MrExcel 3-16-11 Fig02

Customize the Macro for Your Workbook

There are a couple of questions you will have to answer to customize this macro. You will need the worksheet name and the column number where the days are stored. In my example, the worksheet is simply “Sheet1” and the days are stored in column B, which is column 2.

Then copy and paste the following lines into your macro:

   Private Sub Workbook_Open()
      Worksheets(“Sheet1”).Select
      x = Day(Date)
      Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
   End Sub

You should put your actual sheet name in the two places where I have “Sheet1” and put the correct column number where the bold 2 appears above.

Press Alt Q to close the VBA Editor and return to Excel. Save your workbook and close it. When you open the workbook, Excel should move the cell pointer to the correct date — in this case to 16 — as shown in Figure 3.

Fig. 3

MrExcel 3-16-11 Fig03

Improving the Macro

Notice in Figure 3 that the macro selected the row with the 16, but it did not scroll that row to the top of the worksheet. Add one line of code (highlighted in bold here) to have the found cell scroll to the top left of the screen.
   Private Sub Workbook_Open()
     Worksheets(“Sheet1”).Select
     x = Day(Date)
     Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
     Application.Goto Selection, True
End Sub

With the extra line, Excel will scroll to the day, as illustrated in Figure 4:

Fig. 4

MrExcel 3-16-11 Fig04

What if Your Date Is Missing?

There are times when you will open a workbook and there will be no data for that date. For example, say you open your workbook on the 21st and the data for that date doesn’t exist. If the macro cannot find a cell that contains 21, then you want to handle the error. Fix the error by typing this additional line of code (see bold):

      Private Sub Workbook_Open()
        Worksheets(“Sheet1”).Select
        x = Day(Date)
       On Error Resume Next
       Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
       Application.Goto Selection, True
    End Sub

Looking for Dates Instead of Days

The problem is a bit trickier if you are looking for an actual date. The Find command in the macro needs to be looking for the correct date format. Below (Figure 5) is a table showing how to adjust the macro for various date formats.

Fig. 5

MrExcel 3-16-11 Fig05

The following macro will look for a date stored in the Short Date format:
Private Sub Workbook_Open()
    Worksheets(“Sheet1”).Select
    x = Format(Date, “Short Date”)
    On Error Resume Next
    Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
    Application.Goto Selection, True
End Sub

Excel VBA Macro Resources

Macros dramatically increase what is possible with Microsoft Excel. I will be doing a series of Webcasts for CFO on May 19, August 16, and October 18 to introduce VBA. In the meantime, there is a community of passionate Excel VBA experts who regularly check in at the MrExcel Message Board. About 10,000 times each year, someone will provide a snippet of code to solve problems such as this one. You can simply copy and paste the code into your workbook’s VBA window; it is a great way to get started with VBA. It is free to register and free to participate. Check it out here.

CFO contributing editor Bill Jelen is the author of 32 books about Excel, including three editions of VBA & Macros for Microsoft Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.