VBA & Macro Problems

Move Excel Rows to Worksheets Based on a Dropdown with VBA

Adam DavisAdam Davis Nov 18, 2026 870 views

Question details

Move an entire row to a corresponding worksheet when a name is selected from a dropdown in column L.

Move Excel Rows to Different Worksheets Based on a Dropdown
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the source sheet's code window

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

2
Adjust the mappings

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.

3
Save and test

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.

Use a worksheet change event to move the selected row
Check the destination column: The code finds the next row using column A. If destination data rows can have a blank in column A, use a consistently populated column instead. Protected sheets or existing content outside the expected data area can prevent or affect the move.
Free Microsoft Office alternative

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.

Open and edit spreadsheet data used in dropdown-based workflowsCompatible with Microsoft Excel XLSX filesUse a familiar interface for everyday spreadsheet tasks
microsoft office alternative - wps office

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.