How to Automatically Copy or Move Excel Rows Based on Drop-Down Values
Question details
The user needs to automatically copy or move entire rows to different worksheets based on specific selections made in a drop-down menu.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a tracking log where rows must automatically sort into an 'Assistance' worksheet when marked 'Yes', or move completely to a 'Stage Won' worksheet when the status changes to 'Won'.
- Observed behavior
- Rows currently remain static. The goal is to trigger an automated VBA script to copy or move the row and optionally delete the original once the drop-down value is updated.
Ensure you have a backup of your workbook before applying VBA scripts, and confirm that your file is saved as an Excel Macro-Enabled Workbook (*.xlsm).
Use a Worksheet_Change Event Macro
Apply a VBA macro that runs automatically whenever you modify a drop-down cell, routing the row to the corresponding worksheet.
This solution relies on the Worksheet_Change event. This special type of macro runs in the background continuously, monitoring specific columns (like the Stage or Assistance columns). When it detects a change, it evaluates the drop-down value and copies or moves the entire row instantly.
Right-click the sheet tab at the bottom of your screen that contains your drop-down menus and select 'View Code' to open the VBA editor.
Paste your Worksheet_Change VBA code into the blank window. Ensure the script monitors the exact columns containing your drop-downs (e.g., Range("G:G")) and targets the exact names of your destination worksheets.
If you want the row to be moved rather than just copied, ensure your VBA code includes the 'Target.EntireRow.Delete' command immediately after the copy command.
Go to File > Save As, and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown. If you skip this step, your macro will be lost when you close the file.
Automate Your Spreadsheets Seamlessly with WPS Office
WPS Spreadsheet fully supports VBA macros, allowing you to run powerful automation scripts like copying and moving rows based on drop-down values just as efficiently as Microsoft Excel.
- 1. Enable the Developer Tab: Open WPS Spreadsheet, navigate to the settings, and ensure the Developer tools are enabled to access advanced macro features.
- 2. Access the VBA Editor: Click the Developer tab in the top ribbon and select 'VB Editor', or use the ALT+F11 shortcut.
- 3. Apply and Run Your Code: Paste your Worksheet_Change event code into the relevant Sheet module, save your workbook as a Macro-Enabled file, and instantly trigger your automations.
Frequently Asked Questions
Why isn't my macro triggering when I change the drop-down value?
This usually happens if the macro was placed in a standard module instead of the worksheet module. Right-click the specific worksheet tab, select 'View Code', and ensure the Worksheet_Change code is pasted there. Also, verify that macros are enabled in your security settings.
Can I move the row instead of just copying it to the new worksheet?
Yes. In your VBA script, immediately after the line that copies the row to the destination sheet (e.g., Target.EntireRow.Copy), you need to add a command to delete the original row (Target.EntireRow.Delete).
How do I add more conditions for different drop-down values?
You can add additional 'If' statements or use a 'Select Case' block within the same Worksheet_Change macro. Just specify the new target value to check and the corresponding worksheet name where the row should be sent.
