Write an Excel VBA Macro That Cleans and Formats Your Recurring Report
You get a commented VBA macro built from your own column headers, with backup steps, install instructions and a post-run checklist.
Prompt
You are an Excel automation specialist who writes VBA for people who have never opened the VBA editor.
Your job: write a macro that cleans and formats a report I receive every week or month. I will paste the column headers and 5-10 sample rows right after this prompt. Before writing any code, ask me up to 4 short numbered questions in one message (for example: what to do with blank rows, which columns are dates or money, how to sort, whether the sheet name changes) and wait for my answers.
Rules:
1. Put a plain-English comment above every block saying what it does and why.
2. Find columns by header name, not by letter, so the macro still works if columns move.
3. Never change the original data: copy it to a new sheet named Cleaned and work there.
4. Use only built-in VBA, no add-ins or external references.
5. End with a message box showing how many rows were kept and removed.
Output format:
1. Before you start: tell me to save a backup copy and save the file as .xlsm (Excel Macro-Enabled Workbook).
2. The full macro in one code block.
3. How to install and run it: numbered steps from Alt+F11 to clicking Run.
4. What to check after the first run: 3-5 bullets.
End with one line listing the assumptions you made about my data and what could break the macro.Example output
Tips and common edits
Why this works
- Asking questions first means the macro matches your real rules for blanks, dates and sorting instead of generic guesses.
- Finding columns by header name keeps the macro working when the export adds or moves a column.
- Copying to a Cleaned sheet plus the backup and .xlsm steps protects your raw data on the first run.
Use this when you clean the same Excel export by hand every week. Have the header row and a few sample rows ready to paste (remove sensitive data). Works in ChatGPT, Claude or Gemini.
Common edits
- Ask it to add a button on the sheet that runs the macro with one click.
- Ask for an extra step that saves the Cleaned sheet as a dated PDF or CSV.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Combine and Clean Multiple CSV or Excel Files into One Table with Power Query
You get a header mapping table, numbered Power Query clicks, commented M code that fixes mismatched columns and duplicates, and refresh steps for new files.
Batch Rename Files in a Folder with a Script You Can Preview First
You get a PowerShell or Bash rename script that shows every old-to-new name before changing anything, writes an undo log, and includes plain run steps.
Build an Excel Formula from a Plain-English Description of What You Need
You get a working Excel formula, versions for older Excel and Google Sheets, a one-row worked example, and a table of the blank and error cases it handles.
Write a Google Apps Script to Automate a Repetitive Google Sheets Task
You get a commented Google Apps Script built around your sheet names, columns and timing, plus install steps, a test plan on a copy, and undo steps.
Find and Fix Data Quality Issues with Ready-to-Run SQL Checks
Paste your schema and get SQL queries that catch duplicates, NULLs, orphaned foreign keys and out-of-range values, each with what a bad result means and a fix.
Write SQL Window Function Queries for Running Totals, Ranks and Period Changes
Get a commented SQL window function query for running totals, rankings or period comparisons, with each PARTITION BY, ORDER BY, tie and NULL choice explained.