How to Give Proper Instructions to ChatGPT for VBA (Detailed Guide)

Why are instructions important?

Think of ChatGPT like a helpful assistant. If you say “make a report,” it doesn’t know:

• Where is the data?
• What should the report include?
• What file or sheet to use?

So, you need to explain your goal + file structure + steps you want automated.

Let’s go through everything you should include.

---

1. Say What You Want the Code to Do

This is the first thing you must explain.

🟢 Bad instruction:

“Make a macro for Excel”

✅ Good instruction:

“I want to make a macro that copies data from Sheet1 to Sheet2, but only the rows where column B is greater than 100. Also, I want it to run when I click a button.”

---

2. Mention the File or Folder Path (if needed)

If your macro needs to open a file or save something, give the full file path.

✅ Example:

“I want to open a workbook located at:
C:\Users\John\Documents\Reports\sales_data.xlsx”

“Then copy data from this workbook to the active one.”

ChatGPT will then include that path in the code.

---

3. Mention the Sheet Names

Always say the exact name of the sheet(s) you are working with.

❌ Don’t say:

“Copy from the first sheet.”

✅ Instead say:

“Copy from the sheet named ‘Data2024’ and paste into the sheet named ‘Summary’.”

Even better:

“In ‘Data2024’ (source), copy columns A to D, starting from row 2, and paste into ‘Summary’ starting at cell A2.”

---

4. Give Cell Ranges

Be as specific as possible with cell ranges:

✅ Examples:

• “Copy from A2:D100”
• “Start from cell A2 and go down until the first empty row”
• “Paste into cell B2 of the sheet named ‘FinalOutput’”
• “Highlight all cells in column C that are greater than 100”

---

5. Explain Any Conditions or Filters

If you want the code to do something only if a condition is true, mention that clearly.

✅ Examples:

• “Only copy rows where column E is not empty”
• “Delete rows where column A has the word ‘Closed’”
• “Skip rows where column B = 0”

---

6. Say What to Do with Existing Data

Should it replace, append, or skip existing data?

✅ Examples:

• “Clear the destination sheet before pasting”
• “Paste below the last used row”
• “Do not paste headers if already present”
• “If the same ID exists, update the row instead of adding a new one”

---

7. Mention Buttons or Shortcuts (if needed)

If you want to trigger the macro with a button or shortcut:

✅ Examples:

• “Create a button on sheet ‘Dashboard’ to run the macro”
• “Assign this macro to run when the workbook opens”
• “Use the shortcut Ctrl+Shift+R to run the macro”

---

8. Give an Example Dataset (Optional but Helpful)

You can also paste a small sample of your data to help ChatGPT understand better.

✅ Example:

Sheet: “Data2024”

A	B	C
Name	Score	Status
John	120	Passed
Alice	80	Failed

“I want to copy only the rows where ‘Score’ is greater than 100 to another sheet.”

---

9. Mention the Office App and Version

Sometimes, the version of Excel or Word matters. You can say:

✅ Example:

“I’m using Microsoft Excel 2019 on Windows 10”

---

10. Ask for Comments in the Code (If You Want to Learn)

If you are learning, you can say:

✅ Example:

“Please add comments to explain each line of the VBA code.”

ChatGPT will then give you something like:

Sub CopyHighScores()
    ' Define the worksheets
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Sheets("Data2024")
    Set targetSheet = ThisWorkbook.Sheets("Summary")

    ' Define row variables
    Dim i As Long, lastRow As Long
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "B").End(xlUp).Row

    ' Start pasting from row 2
    Dim targetRow As Long
    targetRow = 2

    ' Loop through the data
    For i = 2 To lastRow
        If sourceSheet.Cells(i, 2).Value > 100 Then ' Check if Score > 100
            sourceSheet.Rows(i).Copy Destination:=targetSheet.Rows(targetRow)
            targetRow = targetRow + 1
        End If
    Next i
End Sub

---

Putting It All Together – Sample Perfect Instruction

Here is how a perfect instruction to ChatGPT might look:

---

I want to create a VBA macro in Excel 2021 on Windows that does the following:

1. Open a file located at:
   C:\Users\Anna\Documents\Q1\Report.xlsx
2. From the sheet named “RawData”, copy rows where column D (Status) = “Completed”
3. Copy columns A to F, starting from row 2 (skip header)
4. Paste the data into a sheet named “MasterData” in my current workbook, starting from cell A2
5. Clear all old data in “MasterData” before pasting
6. Add a message box at the end saying: “Data import complete!”
7. Please add comments to explain the code

---

Conclusion

Giving clear and detailed instructions to ChatGPT is the key to getting useful VBA help. You don’t have to be a tech expert. Just follow these tips:

✅ Say exactly what you want
✅ Mention file paths, sheet names, and cell ranges
✅ Explain any conditions or filters
✅ Say how you want the result handled
✅ Ask for comments if you want to learn

Once you practice giving detailed requests, ChatGPT can help you build any VBA macro you can imagine — even if you’re just starting out. Want to try giving me a sample task now? I can walk you through it!