📥 Download Day 5 Practice Workbook
Get the companion Excel workbook containing the VBA master data and department splitting templates.
Download Workbook
VBA (Visual Basic for Applications) allows you to automate almost anything in Excel. While writing VBA code from scratch can take months to learn, you can write natural language instructions for an AI tool to write perfect code for you in seconds.
1. The VBA Editor Workflow
Once you have code generated by ChatGPT or Gemini, follow these steps to place and run it inside Excel:
- Open your Excel workbook.
- Press Alt + F11 *(Mac: Option + F11)* to open the built-in VBA Developer Editor.
- In the top menu bar, click Insert, then select Module. A blank code-editor pane will open.
- Copy the VBA script from the AI and paste it directly into this blank pane.
- To run the code immediately, click the green Run (Play) Button in the toolbar, or press F5.
- Close the VBA Editor window. You'll see the script has completed the task on your sheets!
⚠️ CRITICAL SAVING WARNING: Workbooks containing macros cannot be saved as normal files. You MUST save your file as an Excel Macro-Enabled Workbook (*.xlsm). If you save it as a standard .xlsx, all macros will be deleted permanently.
2. AI Prompting Best Practices
To get accurate, bug-free VBA scripts from AI, your prompts should include:
- Input setup details: Mention your sheet name (e.g. "MasterData") and the specific columns.
- Logical task steps: Break down what you want Excel to do step-by-step.
- Safety requests: Ask the AI to prevent screen flickering and turn off alerts for faster processing.
📋 Copy-Paste Prompt Template
"Act as an expert Excel VBA developer. I have an Excel sheet named 'MasterData'. Column C contains the 'Department'. Write a clean, commented VBA script that loops through all rows, finds unique departments, creates a new worksheet named after each department, and copies the corresponding rows to their respective sheet. Make sure to turn off ScreenUpdating and DisplayAlerts for speed, and turn them back on when done."
3. Example VBA Code Output
Here is the exact code generated by the prompt above. You can paste this module directly into Excel to test it:
Sub SplitDataByDepartment()
Dim wsMaster As Worksheet
Dim wsNew As Worksheet
Dim uniqueDeps As Collection
Dim cell As Range
Dim depName As String
Dim lastRow As Long
Dim i As Long
' Optimize performance
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wsMaster = ThisWorkbook.Sheets("MasterData")
lastRow = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
Set uniqueDeps = New Collection
' 1. Collect all unique departments from column C
On Error Resume Next
For i = 2 To lastRow
depName = wsMaster.Cells(i, 3).Value
If depName <> "" Then
uniqueDeps.Add depName, Key:=depName
End If
Next i
On Error GoTo 0
' 2. Loop through unique departments, create sheets, and copy rows
For Each Item In uniqueDeps
depName = Item
' Delete sheet if it already exists
On Error Resume Next
ThisWorkbook.Sheets(depName).Delete
On Error GoTo 0
' Create new sheet
Set wsNew = ThisWorkbook.Sheets.Add(After:=wsMaster)
wsNew.Name = depName
' Copy headers
wsMaster.Rows(1).Copy Destination:=wsNew.Rows(1)
' Copy matching data rows
wsMaster.Range("A1:E" & lastRow).AutoFilter Field:=3, Criteria1:=depName
wsMaster.Range("A2:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy Destination:=wsNew.Range("A2")
wsMaster.AutoFilterMode = False
' Autofit columns
wsNew.Columns.AutoFit
Next Item
' Reset optimization settings
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Successfully split master data into " & uniqueDeps.Count & " sheets!", vbInformation
End Sub