Write a Commented VBA Macro to Automate a Repetitive Excel Task

Get a commented, beginner-safe VBA macro built around your sheet names, ranges and trigger, with install steps, test advice and a backup warning.

You getBackup warning, what the macro does and the full code in one code block

Prompt

Prompt
You are an Excel VBA developer who writes safe, readable macros for non-programmers.

Write a VBA macro that automates a repetitive task in my workbook. Before writing code, ask me these questions in one message and wait for my answers:
1. What task do you repeat, step by step, as you do it by hand?
2. What are the exact sheet names and the ranges or columns involved? Does the number of rows change?
3. How should it run: a button, a keyboard shortcut, on workbook open, or when a cell changes?
4. Which Excel version and platform (Windows or Mac)?

Rules:
- Comment every block in plain English.
- Find the last used row dynamically; never hard-code row counts.
- Use Option Explicit, declare every variable, and add error handling that shows a clear message instead of crashing.
- Turn screen updating off during the run and always restore it, even after an error.
- Ask for confirmation before deleting or overwriting any data.

Output format:
1. Backup warning: one line telling me to save a copy first, because macro changes cannot be undone with Ctrl+Z.
2. What the macro does: 3-5 bullets.
3. The full code in one code block.
4. Install steps: numbered, from opening the VBA editor to saving as .xlsm and setting up the trigger.
5. How to test it safely.

End with one line listing your assumptions and anything I should confirm.

Example output

At a glanceInstalling the CopyClosedDeals macroFive steps from VBA editor to a working button
  1. Open the VBA editorPress Alt+F11
  2. Insert a moduleInsert > Module, then paste the code
  3. Save as .xlsmExcel Macro-Enabled Workbook
  4. Draw a buttonRectangle on Raw Data labeled Copy Closed Deals
  5. Assign the macroRight-click > Assign Macro > CopyClosedDeals

Example for: a sales coordinator who copies Closed deals from "Raw Data" to "Closed Deals" every Monday, columns A-F, status in column E, button trigger, Excel 365 on Windows.

1. Backup warning: Save a copy of the workbook before the first run. Macro changes cannot be undone with Ctrl+Z.

2. What the macro does

  • Asks you to confirm before clearing old results
  • Clears "Closed Deals" from row 2 down, keeping your headers
  • Scans every row in "Raw Data", however many there are
  • Copies rows where column E says Closed
  • Shows how many deals were copied

3. Code

Option Explicit

' Copies all Closed deals from Raw Data to Closed Deals
Sub CopyClosedDeals()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim nextRow As Long
    Dim i As Long

    On Error GoTo CleanFail

    Set wsSource = ThisWorkbook.Worksheets("Raw Data")
    Set wsTarget = ThisWorkbook.Worksheets("Closed Deals")

    ' Confirm before clearing old results
    If MsgBox("Clear Closed Deals from row 2 down and refresh?", vbYesNo + vbQuestion) = vbNo Then Exit Sub

    Application.ScreenUpdating = False

    ' Clear old data, keep headers in row 1
    wsTarget.Range("A2:F" & wsTarget.Rows.Count).ClearContents

    ' Find the last used row in column A
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    nextRow = 2

    ' Copy each row where Status (column E) is Closed
    For i = 2 To lastRow
        If Trim(wsSource.Cells(i, "E").Value) = "Closed" Then
            wsSource.Range("A" & i & ":F" & i).Copy wsTarget.Range("A" & nextRow)
            nextRow = nextRow + 1
        End If
    Next i

    MsgBox (nextRow - 2) & " closed deals copied.", vbInformation

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

CleanFail:
    MsgBox "Something went wrong: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

4. Install steps

  1. Press Alt+F11 to open the VBA editor.
  2. Click Insert > Module and paste the code.
  3. Close the editor and save as Excel Macro-Enabled Workbook (.xlsm).
  4. On "Raw Data", go to Insert > Shapes, draw a rectangle and label it Copy Closed Deals.
  5. Right-click the shape > Assign Macro > CopyClosedDeals > OK.

5. Test it safely

  • Run it on the backup copy first.
  • Compare the count in the message box with a filter on column E.
  • Spot-check the first and last copied rows.

Assumptions: headers sit in row 1 on both sheets, column A has no blank cells mid-list, and the status text is exactly "Closed". Confirm if any of these differ.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for sheet names, ranges and trigger first means the code runs on your real workbook instead of a generic example.
  • Rules for dynamic last row, Option Explicit and error handling prevent the most common macro failures.
  • The backup warning and confirmation prompt protect data, since VBA changes cannot be undone.

Use when you repeat the same copy, filter or cleanup steps every week. Have your sheet names and column letters ready. Works in ChatGPT, Claude or Gemini; paste the code into Excel's VBA editor.

Common edits

  • Ask for a Google Sheets Apps Script version instead of VBA.
  • Add a rule to log each run's date and row count to a separate sheet.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets