VBA Auto‑Routing of Google Form Rows to Specific Excel Sheets

by

Summary

Goal: Automatically route incoming Google Form responses (imported into an Excel workbook) to separate sheets in a secondary workbook based on a choice field.

Approach: Use a VBA event macro in the source workbook that fires when new rows are added (via Worksheet_Change) and copies the row to the appropriate sheet in the target workbook. Power Query can pull the whole dataset, but it cannot push rows conditionally, so VBA is the simplest reliable solution for real‑time segregation.

Root Cause

  • The source workbook receives all responses in a single table; there is no built‑in mechanism to split rows on write.
  • Google Forms → Excel integration is a one‑way import; Excel must decide where to place each row after the import occurs.

Why This Happens in Real Systems

  • Batch data ingestion: Many pipelines dump raw events into a landing zone (here: Sheet1) before downstream processing.
  • Lack of schema‑aware routing: Without custom logic, the landing zone merely stores everything, leaving routing to downstream jobs or scripts.
  • Event‑driven automation: Real‑time systems rely on change events (file watchers, database triggers, VBA Change events) to move data promptly.

Real-World Impact

  • Data silos: Analysts waste time filtering rows manually.
  • Reporting delays: Dashboards that depend on segregated data cannot refresh automatically.
  • Error proneness: Manual copy‑paste introduces duplication or missing records.

Example or Code (if necessary and relevant)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim srcRow As Long, optCol As Long, optVal As Variant
    Dim wbDst As Workbook, wsDst As Worksheet
    Dim srcWs As Worksheet

    Set srcWs = Me                          'Sheet with raw data
    optCol = 3                              'Column with the option choice (e.g., C)

    'Exit if change is not a whole new row addition
    If Target.Rows.Count  1 Or Target.Columns.Count  srcWs.UsedColumnCount Then Exit Sub

    srcRow = Target.Row
    optVal = srcWs.Cells(srcRow, optCol).Value

    If IsEmpty(optVal) Then Exit Sub

    Set wbDst = Workbooks.Open("C:\Path\To\TargetWorkbook.xlsx")

    Select Case optVal
        Case "Option 1": Set wsDst = wbDst.Sheets("Sheet1")
        Case "Option 2": Set wsDst = wbDst.Sheets("Sheet2")
        Case "Option 3": Set wsDst = wbDst.Sheets("Sheet3")
        Case Else: GoTo Cleanup
    End Select

    wsDst.Rows(wsDst.Cells(wsDst.Rows.Count, 1).End(xlUp).Row + 1).Value = _
        srcWs.Rows(srcRow).Value

Cleanup:
    wbDst.Save
    wbDst.Close False
End Sub

How Senior Engineers Fix It

  • Encapsulate routing logic in a reusable module, allowing easy addition of new options.
  • Add error handling (On Error GoTo) to avoid corrupting the source workbook if the target file is locked.
  • Use Application.EnableEvents = False while writing to the destination to prevent recursive triggers.
  • Store the target path in a hidden named range or configuration sheet rather than hard‑coding it.
  • Consider a Power Automate flow if the organization prefers a no‑code cloud solution; otherwise, keep the VBA in a signed macro to satisfy security policies.

Why Juniors Miss It

  • They often try to force Power Query to write data conditionally, not realizing it’s a read‑only ETL tool.
  • They may place the macro in a standard module instead of the worksheet’s code pane, so the Worksheet_Change event never fires.
  • They forget to disable events during the copy, causing infinite loops.
  • They hard‑code file paths or sheet names, leading to breakage when the environment changes.

Leave a Comment