Help me write, repair, or explain an Excel VBA macro. Workbook and worksheet layout: [WORKBOOK LAYOUT] Task the macro must perform: [AUTOMATION TASK] Example rows, values, or expected result: [EXAMPLE DATA AND RESULT] How it will be run: [RUN METHOD] Existing VBA and exact error, if any: [EXISTING CODE OR ERROR] Constraints and safety requirements: [CONSTRAINTS]
Return VBA that is ready to paste into the correct place: a standard module, worksheet module, ThisWorkbook, or a UserForm module, based on [RUN METHOD]. State that location explicitly. Use fully qualified workbook, worksheet, and range references; do not rely on ActiveSheet, Selection, or hard-coded last-row assumptions unless I request them. Determine the last row from a populated column that I identify, and handle a header-only sheet, blanks within data, and no matching records. Preserve formulas, formatting, filters, and source data unless [CONSTRAINTS] explicitly permits changes. If the macro changes or deletes cells, include a confirmation or dry-run preview where appropriate and explain how to back up the workbook. Use Option Explicit, meaningful variable types, and targeted error handling that restores Application settings such as ScreenUpdating, EnableEvents, and Calculation if they are changed. Do not claim the macro works with external files, protected sheets, tables, or permissions unless my details establish that. Return: 1. Assumptions and where to paste the code. 2. Complete VBA code in one block. 3. A plain-English walkthrough. 4. Test cases, including an empty or malformed-data case. Before answering, check that all variables are declared and every range reference points to a valid worksheet. Flag any destructive step or missing workbook detail. Ask up to 3 clarifying questions only if a required input is missing.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [WORKBOOK LAYOUT] | Describe workbook names, sheet names, headers, table names, and the columns containing relevant data. | Workbook SalesTracker.xlsm, sheet Orders. Headers in row 1: A Order ID, B Region, C Order Date, D Amount, E Status. Data starts row 2; column A is always populated for real orders. |
| [AUTOMATION TASK] | State exactly what the macro should do and when it should stop. | On a button click, copy rows with Status = Closed and Order Date in the current month to sheet Monthly Report, then total Amount in cell H2 without changing Orders. |
| [EXAMPLE DATA AND RESULT] | Provide a few representative rows and the expected output or changed cells. | A2 1001, East, 9/3/2026, 240, Closed should copy. A3 1002, West, 8/30/2026, 180, Closed should not. H2 should total copied amounts. |
| [RUN METHOD] | Explain how the macro should run, such as a button, keyboard shortcut, workbook open, or worksheet change. | A Forms button on the Monthly Report sheet. |
| [EXISTING CODE OR ERROR] | Paste current VBA code and the exact error message, or write None. | None; start from scratch. |
| [CONSTRAINTS] | List data-preservation rules, protected-sheet limits, performance needs, and any allowed changes. | Clear only previous report rows A2:E500 before copying; preserve headers and formatting; show a message if no rows match. |
How to use
- Map the worksheet headers and identify the column that reliably defines the last row.
- State exactly which cells may change, especially if the macro clears, overwrites, or exports data.
- Paste the macro into the stated VBA location and test it on a copy of the workbook with normal and empty data.
- Follow up with: "Modify this macro to write a timestamp and number of copied rows to cells J2 and J3 without changing the existing report layout."
Variations
Formula repair
Use this when formulas are wrong but a macro may not be necessary.
Diagnose and fix this Excel formula problem. Workbook context: [SHEET AND COLUMNS]. Current formula: [CURRENT FORMULA]. Expected result for these sample rows: [SAMPLE ROWS AND RESULTS]. Excel version: [EXCEL VERSION]. Explain the likely failure, then provide the corrected formula and a version suitable for filling down or using in an Excel Table. Do not change values manually or invent missing data. Before answering, test the formula logic against every supplied sample, including blanks and errors.
Import CSV macro
Use this to import structured CSV files consistently into an Excel workbook.
Write an Excel VBA macro to import CSV files from [FOLDER OR FILE RULE] into [DESTINATION SHEET]. The CSV headers are [CSV HEADERS], and the destination rules are [DESTINATION RULES]. State whether it appends, replaces, or rejects duplicates; use this duplicate key if applicable: [DUPLICATE KEY]. Return complete code, where to paste it, and test cases for no files, a missing header, blank rows, and an already-imported file. Do not delete source files or overwrite existing data unless explicitly authorized. Check that file paths and ranges are fully qualified.
Worksheet event
Use this for automatic validation or formatting when someone edits a worksheet.
Create an Excel Worksheet_Change VBA procedure for sheet [SHEET NAME]. It should react only when users edit [WATCHED RANGE] and then [REQUIRED BEHAVIOR]. Existing formulas or validation to preserve: [PRESERVATION RULES]. Return code for the worksheet module, an explanation of event recursion prevention, and test cases. Handle pasted multi-cell edits and blanks. Do not use Select or ActiveCell, and ensure EnableEvents is restored even after an error. Flag any requirement that could slow a large workbook or overwrite user data.
Tips
- Tell the model where headers begin and which column is guaranteed to be populated; this determines whether a last-row calculation is safe.
- Place event-driven code in the relevant worksheet module, not a standard module, and guard it with Intersect so unrelated edits do not trigger it.
- Never test a macro that clears, moves, or writes data against the only copy of a workbook; use a duplicate with representative rows.
- Avoid ActiveWorkbook and Selection in reusable macros because the macro can act on the wrong file when users switch windows.
FAQ
Can AI write VBA macros that I can paste into Excel?
Yes, if you provide workbook structure, expected behavior, and the macro’s run method. Test the result on a workbook copy before using it on live data.
Where do I paste VBA code?
Standard automation macros usually belong in a standard module. Worksheet_Change code belongs in that worksheet’s code module, while workbook-open code belongs in ThisWorkbook.
Why does a VBA macro work on one sheet but not another?
Unqualified references such as Range("A1") can point to whichever sheet is active. Use explicit workbook and worksheet references to prevent that.