VBA & Macro Problems

How to Select and Scroll to Today's Date When Opening an Excel Workbook

Khadija Khan Oct 1, 2026 868 views

Question details

The user wants Excel to automatically locate, scroll to, and select the cell containing the current date as soon as the workbook is opened.

How to Automatically Select Today's Date When an Excel Workbook Opens

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing a daily tracker or schedule where dates are listed across a row (often with a frozen first column), requiring quick access to the current day's entry.
Observed behavior: The user currently has to manually scroll through the dates to find and select today's date every time the file is opened.

Before you start

Ensure your date row contains actual Excel date values rather than text strings, and remember that automating this action requires saving your file as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a VBA Macro to Auto-Select Today's Date

Add a simple script to the Workbook_Open event to automatically find and select the current date when the file loads.

By utilizing Excel's built-in Visual Basic for Applications (VBA), you can trigger an automatic search for today's date the moment the workbook opens. This method relies on the 'Workbook_Open' event.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Access the ThisWorkbook Module

In the Project Explorer pane on the left side of the window, locate your workbook's name. Double-click on 'ThisWorkbook' to open its code window.

Insert the Macro Code

Paste the following code into the blank window: Private Sub Workbook_Open() Dim ws As Worksheet Dim r As Range Set ws = ActiveSheet Set r = ws.Rows(1).Find(Date, LookIn:=xlFormulas, LookAt:=xlWhole) If r Is Nothing Then MsgBox "Today's date was not found", vbInformation Else r.Select End If End Sub Note: Change 'Rows(1)' to the specific row number where your dates are located if they are not in the first row.

Save as a Macro-Enabled Workbook

Close the VBA editor. Go to File > Save As, and change the file format to 'Excel Macro-Enabled Workbook (*.xlsm)'. When you reopen this file, ensure you click 'Enable Content' to allow the macro to run.

Use a VBA Macro to Auto-Select Today's Date

Error Handling Included: The code includes a safety check. If today's date is not found in the row, it will display a polite message box instead of causing a macro error.

Solution 2

Alternative: Use Conditional Formatting as a Visual Indicator

If you cannot or prefer not to use macros, use conditional formatting to highlight today's date so it stands out visually.

Automate Your Spreadsheets Seamlessly with WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to run scripts like the auto-select date macro effortlessly. Enjoy a robust, lightweight alternative to handle complex spreadsheet automation.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing tracking spreadsheet.
2. Access the Developer Tools: Navigate to the 'Tools' tab and click on 'Developer' to access the VBA environment.
3. Insert the Workbook_Open Code: Locate 'ThisWorkbook' in the project list, paste the auto-select macro, and save your file as an .xlsm document.

Fully compatible with Microsoft Excel VBA macros and .xlsm formatsBuilt-in developer tools for writing and testing automation scriptsLightweight installation with a highly familiar user interfaceFree to use for everyday spreadsheet management and daily tracking

microsoft office alternative - wps office

Frequently Asked Questions

Why didn't the macro run when I opened the workbook?

Macro security settings might be blocking the code from executing. Ensure you clicked 'Enable Content' in the yellow security warning bar at the top of the screen when you opened the file. You can also adjust your macro settings in the Trust Center.

Can I make the macro search a column instead of a row?

Yes. If your dates are listed vertically in a column, you can modify the VBA code. Change 'ws.Rows(1).Find' to 'ws.Columns(1).Find' (for column A) to search down the column instead of across a row.

Will this macro work if my dates include times?

If your cells contain date and time values (e.g., '10/25/2023 14:30'), the 'xlWhole' parameter in the Find function might fail to match the pure 'Date' variable. You may need to change 'LookAt:=xlWhole' to 'LookAt:=xlPart' or format the cells strictly as dates.

Excel & Data Problems