VBA & Macro Problems

How to Add the Report Generation Date to an Excel Cell Using VBA

Ayan Masood Oct 12, 2026 868 views

Question details

The user needs to automatically insert the current generation date into cell A1 of a daily report using an Excel macro, instead of displaying the original template's creation date.

How to Add the Report Generation Date to an Excel Cell with VBA

Product: Excel
Device & OS: not provided
Scenario: Automating daily report generation using a macro-enabled Excel template and a CSV file.
Observed behavior: The report needs to display its actual generation date upon creation rather than the creation date of the template workbook.

Before you start

Ensure your macro-enabled template workbook (.xlsm) is open and that you have identified the VBA script block responsible for saving the newly generated report.

Solution 1Recommended

Insert a Date Command Before the SaveAs Function

Modify the existing report-generation macro to stamp the current date or time into the target cell immediately before the workbook is saved.

To ensure the final report accurately reflects when it was generated rather than when the template was created, you must assign the system date directly to the cell in the VBA macro. Placing this code right before the `SaveAs` command guarantees the date is finalized before the new file is written to the disk.

Open the Visual Basic Editor

Press Alt + F11 in Excel to launch the VBA Editor and locate the module containing your report automation macro.

Locate the Save Command

Scroll through your code to find the line where the new report workbook is saved, typically containing `ActiveWorkbook.SaveAs` or `wbk.SaveAs`.

Insert the Date Command

Immediately above the SaveAs line, insert the following code: `ActiveWorkbook.Worksheets(1).Range("A1").Value = Date`. If your code uses a specific workbook variable like `wbk`, use `wbk.Worksheets(1).Range("A1").Value = Date` instead.

Use 'Now' for Date and Time

If you need the exact generation time alongside the date, change `Date` to `Now` (e.g., `Range("A1").Value = Now`).

Save and Run the Macro

Save your module and execute the macro. Check the newly generated report to verify that cell A1 contains the correct timestamp.

Insert a Date Command Before the SaveAs Function

Automation Successful: Your daily reports will now autonomously stamp the exact generation date and time into the specified cell every time the task runs.

Advanced Spreadsheet Automation

Automate Your Spreadsheets Seamlessly with WPS Office

WPS Office provides excellent support for VBA macros and complex scripts, allowing you to run your automated reporting processes and insert generation dates just as easily as you would in Microsoft Excel.

1. Open Your Macro File: Launch WPS Spreadsheet and open your existing macro-enabled template (.xlsm).
2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to view your scripts.
3. Update the Script: Insert the date generation command right before your SaveAs line, exactly as you would in Excel.
4. Run Your Automated Report: Execute your macro to instantly process your CSV data and generate the finalized timestamped report.

Highly compatible with Microsoft Excel (.xlsx, .xlsm, .csv formats)Built-in developer tools for editing and troubleshooting VBA macrosLightweight application that processes large automated datasets efficientlyFree version includes powerful spreadsheet functions and seamless file migration

microsoft office alternative - wps office

Frequently Asked Questions

How do I include the exact time along with the generation date?

In your VBA script, replace the `Date` function with the `Now` function (e.g., `ActiveWorkbook.Worksheets(1).Range("A1").Value = Now`). The `Now` function automatically retrieves both the current system date and the time.

Can I place the date in a specific worksheet rather than the first one?

Yes. If you want to specify a worksheet by name rather than its index number, change `Worksheets(1)` to `Worksheets("YourSheetName")` in your VBA code.

Why is my macro showing an error on the SaveAs line after adding this code?

This usually happens if the workbook variable referenced (like `ActiveWorkbook` or `wbk`) is not correctly defined or active at that stage of the script. Verify that the variable you are applying the date to precisely matches the one being saved.

Why doesn't the date update automatically if I open the final report tomorrow?

Because the macro uses `.Value = Date`, it hardcodes the date into the cell as static text/number. This is ideal for reports as it freezes the creation date permanently, rather than using an `=TODAY()` formula that dynamically changes whenever the file is reopened.

Excel & Data Problems