If you spend more than 20 minutes every week updating, formatting, and saving your inventory report, you should automate it. Excel macros can handle all the repetitive steps — refreshing data, applying formats, and saving a dated copy — with a single click.
What This Macro Will Do
- Refresh all data connections (if you're pulling from a database)
- Sort the inventory list by status (Out of Stock first)
- Apply conditional formatting to the status column
- Save a dated copy to a Reports folder
- Send you a completion message
Step 1 — Enable the Developer Tab
File → Options → Customize Ribbon → check "Developer" → OK. The Developer tab now appears in your ribbon.
Step 2 — Record a Basic Macro First
Before writing VBA, record a macro to understand the structure:
Start recording
Developer tab → Record Macro → name it "RefreshReport" → OK.
Do your manual steps
Sort your data, apply any formatting, click through the steps you do every week.
Stop recording
Developer tab → Stop Recording. Excel has captured all your actions as VBA code.
Step 3 — The Full Automation Macro
Open the VBA editor (Developer → Visual Basic) and paste this code:
C:\Reports\ to the actual folder path where you want to save reports. Make sure the folder exists before running.Step 4 — Add a Button to Run It
Insert → Shapes → Rectangle. Draw a button, right-click → "Assign Macro" → select WeeklyInventoryReport. Now one click runs the whole process.
Step 5 — Schedule It with Windows Task Scheduler (Optional)
To run the report automatically every Monday at 8am:
- Add this line to your macro:
Application.Quitat the end (saves and closes Excel) - Open Windows Task Scheduler → Create Basic Task
- Set trigger: Weekly, Monday, 8:00 AM
- Action: Start a program → browse to Excel.exe with your file as the argument