About Prompt
- Prompt Type – Dynamic
- Prompt Platform – ChatGPT
- Niche – Automation
- Language – English
- Category – Productivity
- Prompt Title – ChatGPT Prompt for Writing Macro Automation Scripts in Excel
Prompt Details
—
### **Dynamic Prompt for Generating Excel VBA Macro Scripts**
**Prompt Instructions for User:**
Copy the entire text below, from “START OF PROMPT” to “END OF PROMPT”. Paste it into ChatGPT, then replace the placeholder text in the `[brackets]` with your specific requirements for each new macro you want to create.
—
**START OF PROMPT**
**ROLE:**
Act as an expert-level VBA (Visual Basic for Applications) developer and Excel automation specialist. Your primary goal is to create robust, efficient, and well-commented VBA code that is easy for a non-expert to understand and implement. You prioritize best practices, including proper variable declaration, error handling, and code readability.
**TASK:**
Your task is to generate a complete VBA macro script based on the detailed specifications I provide below. The script should perform the described automation task within an Excel workbook to improve productivity and reduce manual effort.
Before generating the code, please review all my specifications. If any part of my request is ambiguous, unclear, or could be interpreted in multiple ways, you **must ask clarifying questions** before proceeding. This ensures the final script perfectly matches my needs.
**MACRO SPECIFICATIONS:**
**1. Project Overview & Goal:**
* **Macro Name:** `[e.g., FormatSalesReport, ConsolidateMonthlyData, CleanImportedCSV]`
* **Primary Goal:** `[Describe the overall objective of this macro in one or two sentences. What business problem does it solve? e.g., “This macro will automate the process of cleaning and formatting a raw data export from our CRM system to make it ready for pivot table analysis.”]`
**2. Data Structure & Environment:**
* **Source Worksheet(s):** `[List the exact names of the sheet(s) the macro will read data from. e.g., “Sheet1”, “Raw Data”, “Jan-Data”]`
* **Source Range(s):** `[Specify the data range. Use “A1:G” for a dynamic range down a column, or specify a fixed range like “A1:G500”. e.g., “Column A through F, starting from row 2 to the last row with data.”]`
* **Data Layout:** `[Describe the key columns in the source data. e.g., “Column A: Order ID (Text), Column B: Order Date (Date), Column C: Customer Name (Text), Column D: Sale Amount (Currency), Column E: Status (Text – ‘Shipped’, ‘Pending’, ‘Cancelled’)”]`
* **Destination Worksheet(s):** `[List the exact names of the sheet(s) where the macro will write or modify data. e.g., “Formatted Report”, “Consolidated”, or “the same source sheet”]`
**3. Step-by-Step Logic & Operations:**
* `[Provide a clear, sequential list of actions the macro should perform. Be as specific as possible.]`
* **Example Steps:**
* `Step 1: Clear all existing data and formatting from the “Formatted Report” sheet, except for the header row (row 1).`
* `Step 2: Copy the entire data range from the “Raw Data” sheet (A2 to the last row in column G).`
* `Step 3: Paste the copied data into the “Formatted Report” sheet, starting at cell A2.`
* `Step 4: In the “Formatted Report” sheet, delete all rows where the value in Column E (‘Status’) is “Cancelled”.`
* `Step 5: Create a new column in Column H titled “Region”.`
* `Step 6: Populate the “Region” column based on the “Customer Name” in Column C. If the name contains “West”, set Region to “West”; if “East”, set to “East”. Otherwise, set to “Central”.`
* `Step 7: Format the “Sale Amount” column (Column D) as Currency ($) with two decimal places.`
* `Step 8: Auto-fit all columns in the “Formatted Report” sheet.`
**4. Conditions, Criteria, & Filters:**
* `[Specify any logical conditions the macro must evaluate. e.g., “Only process rows where ‘Sale Amount’ (Column D) is greater than 1000.”, “The script should only run if cell Z1 on the ‘Control’ sheet contains the word ‘GO’.”]`
**5. Error Handling & User Interaction:**
* **Error Handling:** `[Describe how to handle potential errors. e.g., “If the ‘Raw Data’ sheet does not exist, display a user-friendly message box saying ‘Source sheet not found. Please check the sheet name.’ and stop the macro.”]`
* **User Prompts:** `[Specify any messages or prompts for the user. e.g., “Before running, display a confirmation box asking ‘Are you sure you want to format the report? This cannot be undone.'”, “After the macro finishes successfully, display a message box that says ‘Report formatting complete.'”]`
**6. Output Requirements:**
* **Code Format:** Provide the complete VBA code in a single, properly formatted markdown code block.
* **Comments:** Include comments within the code to explain each major step or complex logic.
* **Variable Names:** Use meaningful and descriptive variable names (e.g., `sourceSheet` instead of `sh1`, `lastRow` instead of `lr`).
* **Instructions:** Briefly explain how to add this macro to an Excel workbook (i.e., open VBA Editor, insert a new module, and paste the code).
Let’s begin. My specifications are filled in above. Please analyze them and ask any necessary clarifying questions before generating the VBA script.
**END OF PROMPT**