VBA & Macro Problems

Copy an Excel Row to Another Sheet When a Dropdown Selects 3

Ayan MasoodAyan Masood Nov 13, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the destination worksheet

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.

2
Open the source worksheet's code module

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

3
Customize the trigger and copy behavior

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).

4
Save and test the dropdown trigger

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.

Use a worksheet change event to copy rows when the dropdown value is 3
Appending does not prevent duplicates or keep copies synchronized: Changing a dropdown away from 3 and back to 3 appends another copy. Existing rows already set to 3 are not processed until their cells change. Later edits to other source cells do not update the copied row; synchronization requires additional logic, usually based on a unique record ID.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel .xlsx and .xls spreadsheet formats.Use familiar worksheet tabs, filters, and copy-and-paste controls.Filter dropdown values to 3 and manually copy matching data to another sheet.Use a free, lightweight office suite for everyday spreadsheet work.
microsoft office alternative - wps office

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.