Automation Prompts: Generating VBA Macros and Repeating Tasks Safely

An automation prompt asks the AI to build a macro or a script that does a repetitive Excel task for you. This tutorial covers how to describe a task well enough for the AI to generate working automation, and how to run it safely.

This tutorial is written for Excel with AI — the example prompts below are things you type into an AI assistant such as Microsoft 365 Copilot in Excel or ChatGPT.

Two ways to send these prompts:

• Copilot in Excel can automate tasks inside your workbook, but it builds them with Office Scripts (a modern, cloud-based automation), not classic VBA.
• ChatGPT (or any chat AI) is the usual place to generate VBA macros — you describe the task, copy the VBA it returns into Excel's VBA editor, then run it.

The prompting skills are the same either way — only the automation language the AI produces differs.

Table of Contents

• What Are Automation Prompts?
• Describe the Task Step by Step
• Generate Macros and Scripts
• Repeat Tasks Across Many Sheets
• Safety: Back Up and Test First
• Iterating on a Macro

What Are Automation Prompts?

An automation prompt asks the AI to produce code — usually a VBA macro — that performs a task automatically. Instead of doing a job over and over (formatting every report, cleaning every file), you describe the job once and the AI writes the code to repeat it.

Two mapping facts matter here: macros = VBA in Excel, and Power Automate handles the newer no-code workflows outside a single workbook. A good prompt states which one you want.

Example:

Write a VBA macro that formats the current sheet as a report:
bold the header row, autofit all columns, and set a page
landscape before printing.

Explanation: this is a small, well-defined automation. The AI can generate the VBA and you run it once to apply to any sheet.

Describe the Task Step by Step

Automation only works if the AI understands the full sequence. Break the job into ordered steps — the same order you would do them by hand.

What a good automation description includes:

• The exact starting condition ("for every sheet in this workbook").
• Each action in order.
• The desired end state.

Example:

Write a macro that, for every sheet in the active workbook:
1. deletes any empty columns,
2. freezes row 1,
3. applies a filter to the header row.

Explanation: the prompt lists numbered steps and a clear starting point ("every sheet"). Numbered steps are the clearest way to describe a script, because the AI maps each line of code to one step.

Generate Macros and Scripts

The core automation prompt is "write a macro that..." followed by a clear description of the job.

Common automation requests:

• "Combine all sheets into one master sheet."
• "Convert all formulas in the range to their current values."
• "Email a summary of this sheet to a list of addresses."
• "Automatically split the data into a new sheet per unique customer."

Write a VBA macro to split a large table into separate sheets,
one per unique value in column B (Region), copying the header
row to each new sheet.

Explanation: the prompt names the trigger (unique values in column B), the action (create a sheet per value), and the detail (copy the header). That is enough detail for the AI to structure the loop correctly.

Best Practice: ask the AI to add a comment to each code block and explain in one line how to run the macro. That makes the code usable even if you never touched VBA before.

Repeat Tasks Across Many Sheets

A very common automation need is repeating one job over many sheets or workbooks. You only need to describe the job once and tell the AI to loop it.

For every worksheet in the workbook, center the headers, set
column widths to fit content, and fill blank cells in column
C with "0".

Explanation: "for every worksheet" tells the AI to wrap the actions in a loop. You write the description once and let the macro repeat it — the entire point of automation.

Safety: Back Up and Test First

Code that runs automatically can also change things you did not intend. Automation is powerful, so run it safely.

Safety rules for any macro:

1. Save a backup copy of the workbook before running a new macro.
2. Test on a copy, or on one sheet, before letting it run on the real file.
3. Check the range references — a wrong range can overwrite data.
4. Review the code the AI wrote before running it. Do not run code you do not understand.
5. Know that macros run as a file only if macros are enabled — Excel blocks them by default for a reason.

Best Practice: start with test data. Run the macro on a sample file, confirm the output, then apply it to the real workbook.

Iterating on a Macro

Automation rarely works perfectly on the first run. Treat the error or wrong output as feedback and refine — the same way you iterate on a formula.

Example:

You: "I ran your macro and got an error: 'Out of range' on
the line that references worksheet 2. The macro should only
work on the active sheet."

AI: "That error means the loop is pointing at a sheet that
does not exist. Here is the fixed version that uses
ActiveSheet instead of Sheets(2)..."

Explanation: you reported the error and the constraint (work on the active sheet). The AI corrected the reference. Describing the symptom and the intended behavior is what makes iteration productive.