How to Automatically Create and Save a Daily Excel Overview Report
Question details
The user needs a method to automatically filter a PivotTable to show yesterday's data, save the results into a daily overview workbook, and overwrite the existing file every day for an automated email workflow.
- Product
- Microsoft Excel / Power Automate
- Device & OS
- not provided
- Scenario
- Automating the daily generation of a filtered PivotTable report from form responses and exporting it to an automatically overwritten overview file.
- Observed behavior
- The user currently relies on manual slicers and PivotTables but wants to eliminate manual intervention so the daily report generation, filtering, saving, and overwriting process runs automatically in the background.
Ensure that the Developer tab is enabled in your spreadsheet software to access VBA tools, and confirm you have permission to automate file creation in your target directory or cloud storage.
Use a VBA Macro to Automate Filtering and Saving
Create a custom VBA script to automatically refresh your PivotTable, filter the data to yesterday's date, and overwrite the daily overview file seamlessly.
VBA (Visual Basic for Applications) allows you to automate repetitive tasks natively within your spreadsheet workbook. By tying the script to a Workbook_Open event or triggering it via a scheduled task, you can generate reports without manual clicks.
Open your primary Excel workbook, click the Developer tab on the ribbon, and select 'Visual Basic' to launch the editor.
Right-click on your VBAProject in the project explorer pane, select 'Insert', and choose 'Module' to create a blank script workspace.
Input the VBA code to refresh your PivotTable caches, apply a filter for 'Date - 1' to capture yesterday's data, and use the 'ActiveWorkbook.SaveCopyAs' or 'SaveAs' method pointing to your Daily Overview file path.
To ensure the file saves automatically without asking for permission to overwrite, add 'Application.DisplayAlerts = False' at the beginning of your script and set it back to 'True' at the end.
Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown so your automation code is preserved.
Utilize Office Scripts with Power Automate
Combine Office Scripts and Power Automate to handle cloud-hosted workbooks, filtering data and triggering emails entirely in the background.
Create and Automate Daily Reports in WPS Office
WPS Spreadsheet features robust built-in support for VBA macros, making it an excellent lightweight solution for automating repetitive tasks. You can seamlessly run scripts to filter data, update PivotTables, and save daily overview files without heavy system resource usage.
- 1. Enable Developer Tools: Launch WPS Spreadsheet, navigate to the Options menu, and ensure the Developer tools are enabled to access the Macros ribbon.
- 2. Open the VBA Editor: Click on the Developer tab and select 'Visual Basic Editor' to begin scripting your daily automated report.
- 3. Write and Save the Macro: Input your macro to automatically filter your tables to yesterday's date and save the workbook copy. Save your file in the .xlsm format.
- 4. Execute and Automate: Run your macro manually via the 'Macros' button, or assign it to a shortcut button for one-click daily report generation.
Frequently Asked Questions
Can I run an automated macro without opening the file myself?
Yes, you can use Windows Task Scheduler to trigger a VBScript or a command-line script that opens the spreadsheet program in the background, executes your specific macro, and closes the application.
How do I stop the 'A file named Daily Overview already exists' prompt?
In your VBA script, insert the line 'Application.DisplayAlerts = False' right before your SaveAs command. This tells the application to automatically accept the default choice (overwrite) without showing the prompt to the user.
Does Power Automate require a premium license to run Office Scripts?
Running an Office Script via the 'Run script' action in Power Automate is included with most standard Microsoft 365 business licenses, but triggering complex external connectors or custom APIs may require a premium license.
