Copy an Excel Row to Another Sheet When a Dropdown Selects 3
Question details
Automatically copy data from columns with headings to another worksheet when a row's dropdown value is 3.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains a dropdown column. Selecting option 3 should copy the corresponding row's headed columns to a destination sheet.
- Observed behavior
- The required outcome is an automatic row copy triggered by the dropdown selection. A standard worksheet formula cannot perform a copy or move operation.
Open the workbook in desktop Excel with VBA support, and identify the source sheet, destination sheet, dropdown column, and header row. Decide whether to append each qualifying row or overwrite the same row number, and whether to copy values only or include formatting and validation.
Use a worksheet change event to copy rows when the dropdown value is 3
Use this VBA solution for an automatic copy whenever a dropdown cell changes to 3. The example appends values from headed columns and leaves the source row intact.
This example assumes headings are in row 1, dropdowns are in column D, and the destination worksheet is named Destination. It copies only columns whose heading is nonblank, preserving their original column positions.
The event responds to direct cell changes, including dropdown selections and pasted values. It does not run merely because a formula recalculates.
In Excel, create or select the destination worksheet and name it Destination, or choose another name to use in the code. Place the source headings in row 1 of that sheet, keeping the same column positions. Reserve this sheet for the copied table so unrelated content does not affect the append position.
Right-click the source worksheet tab and select View Code. Paste the following code into that worksheet module, not into a standard module. If a Worksheet_Change procedure already exists there, merge the logic into it rather than adding a second procedure: Private Sub Worksheet_Change(ByVal Target As Range) Const DROPDOWN_COLUMN As String = "D" Const HEADER_ROW As Long = 1 Const DESTINATION_SHEET As String = "Destination" Dim changedCells As Range, triggerCell As Range Dim destination As Worksheet Dim lastCell As Range Dim nextRow As Long, lastColumn As Long, columnNumber As Long Dim previousEvents As Boolean Set changedCells = Intersect(Target, Me.Columns(DROPDOWN_COLUMN)) If changedCells Is Nothing Then Exit Sub previousEvents = Application.EnableEvents On Error GoTo CopyFailed Set destination = ThisWorkbook.Worksheets(DESTINATION_SHEET) If destination Is Me Then Err.Raise vbObjectError + 1, , "Choose a different destination sheet." End If lastColumn = Me.Cells(HEADER_ROW, Me.Columns.Count).End(xlToLeft).Column Application.EnableEvents = False For Each triggerCell In changedCells.Cells If triggerCell.Row > HEADER_ROW Then If Not IsError(triggerCell.Value2) Then If CStr(triggerCell.Value2) = "3" Then Set lastCell = destination.Cells.Find( _ What:="*", After:=destination.Cells(1, 1), _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _ MatchCase:=False, SearchFormat:=False) nextRow = HEADER_ROW + 1 If Not lastCell Is Nothing Then If lastCell.Row >= nextRow Then nextRow = lastCell.Row + 1 End If For columnNumber = 1 To lastColumn If Len(Trim$(CStr(Me.Cells(HEADER_ROW, columnNumber).Value2))) > 0 Then destination.Cells(nextRow, columnNumber).Value2 = _ Me.Cells(triggerCell.Row, columnNumber).Value2 End If Next columnNumber End If End If End If Next triggerCell Application.EnableEvents = previousEvents Exit Sub CopyFailed: Application.EnableEvents = previousEvents MsgBox "The row could not be copied: " & Err.Description, vbExclamation End Sub
In the code, change DROPDOWN_COLUMN, HEADER_ROW, and DESTINATION_SHEET to match your workbook. To overwrite the matching row number instead of appending, replace the block from Set lastCell through its following End If with nextRow = triggerCell.Row. To copy cell contents, formulas, formatting, and validation rather than values only, replace the two-line .Value2 assignment with Me.Cells(triggerCell.Row, columnNumber).Copy Destination:=destination.Cells(nextRow, columnNumber).
Choose File > Save As and save the workbook as Excel Macro-Enabled Workbook (*.xlsm). Reopen it and enable macros only if you trust the workbook and your organization's policy permits it. On the source sheet, change a data row's dropdown to 3, then check that the headed columns appear on the destination sheet.

Review and copy spreadsheet rows with WPS Spreadsheet
WPS Office is a free, lightweight alternative for opening Excel files and manually copying selected rows between sheets. Its familiar spreadsheet interface and Excel-format compatibility make migration straightforward. It does not fix Excel VBA event problems, and this Worksheet_Change workflow should not be assumed to run in every WPS edition.

Frequently Asked Questions
Can an Excel formula copy the row when I select 3?
A formula cannot execute a copy or move operation. If you only need a live display of matching records, Excel versions with FILTER can use a formula such as =FILTER(Source!A2:F1000,Source!D2:D1000=3,"No matches") on the destination sheet. That produces a dynamic result, not an independent copied row.
Where should I paste the VBA code?
Right-click the worksheet tab containing the dropdown and choose View Code. Paste the Worksheet_Change procedure into that sheet's module. It will not run automatically as a worksheet event if placed in a standard module.
Will the example copy formatting and dropdown lists?
No. The default .Value2 assignment copies values only, including calculated results rather than formulas. To include formatting, formulas, and validation, use the cell .Copy Destination:=... replacement described in the customization step.
Why does nothing happen when the dropdown already shows 3?
Worksheet_Change requires a cell change; it does not scan existing values when the workbook opens. Change the dropdown to another option and then back to 3 to test it. Also confirm the workbook is saved as .xlsm, macros are permitted, and the code is in the correct source worksheet module.
Can I use this VBA automation in Excel for the web or WPS Spreadsheet?
Excel for the web cannot run VBA; open the workbook in desktop Excel to use this event macro. WPS macro support depends on the edition and environment, so verify support before relying on Worksheet_Change. For a manual workflow, use the spreadsheet's Data > Filter controls to show rows with value 3, then copy the required data to the destination sheet.




