VBA & Macro Problems

How to Automatically Open an Excel Tracker to the Current Date Using VBA

Camila Milosovich Oct 8, 2026 869 views

Question details

The user wants an Excel equipment tracking workbook to automatically navigate to the column containing today's date when the file is opened.

How to Automatically Open an Excel Tracker to the Current Date

Product: Excel
Device & OS: not provided
Scenario: Managing a daily tracker with many date columns and needing quick access to the current day's data upon opening the file.
Observed behavior: The user needs to implement a VBA solution to automatically search the date headers and select the column matching the current date.

Before you start

Before adding VBA code to your workbook, create a backup copy of your file with dummy data to test the macro safely.

Solution 1Recommended

Create a Workbook_Open VBA Event

Use a Workbook_Open macro to automatically scan your date headers and select today's date upon opening.

By utilizing the built-in Workbook_Open event in VBA, you can instruct Excel to execute a specific script every time the file is launched. The script will loop through a defined row of date headers and select the cell that matches the system's current date.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Access ThisWorkbook

In the Project Explorer pane on the left side of the window, locate your workbook and double-click on 'ThisWorkbook'.

Enter the VBA Code

In the code window, select 'Workbook' from the left dropdown and 'Open' from the right dropdown. Insert code that loops through your date row (e.g., beginning in column AH), compares each cell value with the VBA 'Date' function, and uses 'Select' on the matching cell.

Save as a Macro-Enabled Workbook

Click 'File' > 'Save As' and change the file format to 'Excel Macro-Enabled Workbook (*.xlsm)'. Ensure macros are enabled in your Trust Center settings so the code can run upon opening.

Macro Security: You must save the file in .xlsm format; otherwise, the VBA code will be permanently lost when you close the workbook.

Automate Your Trackers with Macros in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to automate tasks like jumping to today's date in your daily trackers effortlessly.

1. Open your tracker: Launch WPS Spreadsheet and open your daily tracking file.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic' to open the VBA Editor.
3. Apply the Macro: Double-click 'ThisWorkbook', paste your Workbook_Open macro script, and save the document as a macro-enabled file.

Full support for VBA and Excel macrosSeamlessly open and edit .xlsm files100% compatible with Microsoft Excel formatsLightweight and fast performance

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Workbook_Open macro running when I open the file?

Macros might be disabled by default due to security settings. Check your Trust Center or Macro Security settings to enable them, and ensure the file is saved as an .xlsm (Macro-Enabled Workbook).

Can I search for today's date without using VBA?

While you can use Conditional Formatting to highlight today's date or create a Hyperlink with the MATCH function to click and jump to it, only VBA can automatically scroll and select the cell the moment the file opens.

What if my dates are formatted differently than the system date?

The VBA 'Date' function compares the underlying serial value of the date, not the display format. As long as your column headers are actual date values and not formatted as text strings, the macro will successfully find the match.

Excel & Data Problems