Move Excel Rows to Worksheets Based on a Dropdown with VBA
Question details
Move an entire row to a corresponding worksheet when a name is selected from a dropdown in column L.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A source worksheet has a name dropdown in column L, and each name corresponds to a destination worksheet.
- Observed behavior
- Selecting a mapped name should move that row to the next available row on the matching worksheet.
Create the destination worksheets and confirm the dropdown values and sheet names match the mappings in the code. The example assumes row 1 contains headers and column A is filled for every destination data row.
Use a worksheet change event to move the selected row
Run the macro automatically when one mapped value is selected in column L of the source worksheet.
This example maps A, B, and C to Site A, Site B, and Site C. It copies the entire row and then deletes the original, so rows below it shift up. Unmapped values and changes to multiple cells are ignored.
In desktop Excel, right-click the source worksheet tab, select View Code, and paste the following into that worksheet's code window—not a standard module: Private Sub Worksheet_Change(ByVal Target As Range) Dim dest As Worksheet Dim sheetName As String Dim nextRow As Long Dim errorMessage As String If Target.CountLarge <> 1 Then Exit Sub If Target.Column <> 12 Or Target.Row = 1 Then Exit Sub Select Case Trim$(CStr(Target.Value)) Case "A": sheetName = "Site A" Case "B": sheetName = "Site B" Case "C": sheetName = "Site C" Case Else: Exit Sub End Select On Error Resume Next Set dest = Me.Parent.Worksheets(sheetName) On Error GoTo 0 If dest Is Nothing Then MsgBox "Destination sheet not found: " & sheetName, vbExclamation Exit Sub End If If dest Is Me Then Exit Sub On Error GoTo MoveError Application.EnableEvents = False nextRow = dest.Cells(dest.Rows.Count, "A").End(xlUp).Row + 1 Me.Rows(Target.Row).Copy Destination:=dest.Rows(nextRow) Me.Rows(Target.Row).Delete CleanUp: Application.EnableEvents = True If Len(errorMessage) > 0 Then MsgBox errorMessage, vbExclamation Exit Sub MoveError: errorMessage = "The move could not be completed: " & Err.Description & _ vbCrLf & "Check both sheets; the row may have been copied but not deleted." Resume CleanUp End Sub
In the Select Case section, replace A, B, and C with the dropdown names and Site A, Site B, and Site C with their exact destination worksheet names.
Choose File > Save As and save the workbook as Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted for a trusted workbook, then select a mapped name in column L and check the destination sheet.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for an easier transition. Excel VBA event macros may not work the same way in WPS, so test macro-dependent workflows before migrating.

Frequently Asked Questions
Where should I paste the worksheet event code?
Right-click the source worksheet tab in desktop Excel and select View Code. Paste the Worksheet_Change procedure into that sheet's code window.
Why does the row stay on the source sheet?
Check that macros are enabled, the selection is in column L, and the value matches a Select Case entry. The destination sheet must also exist with the mapped name.
Can I copy the row without deleting the original?
Yes. Remove the line Me.Rows(Target.Row).Delete from the code. Each subsequent mapped change can create another copy, so check for duplicates.
Will the macro handle a paste into several dropdown cells?
No. The Target.CountLarge check ignores changes involving more than one cell. Select or change one cell in column L at a time.




