VBA & Macro Problems

How to Move Excel Rows to Another Worksheet Using a Drop-Down List via VBA

Kushani Nimanthika Oct 9, 2026 869 views

Question details

The user wants to automatically transfer a row of data from a main register sheet to a specific worksheet based on the selection made in a drop-down list.

How to Move Excel Rows to Another Worksheet Using a Drop-Down List

Product: Excel
Device & OS: not provided
Scenario: Managing and categorizing data across multiple sheets by selecting a target sheet name from a drop-down menu in a specific column.
Observed behavior: When a worksheet name is selected from the drop-down list, the entire row should be copied to the next available row on the target worksheet and then deleted from the original source worksheet.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the text entries in your drop-down list match your destination worksheet names exactly, including spaces.

Solution 1Recommended

Use a VBA Worksheet_Change Event to Automate Row Moving

Implement a VBA macro that triggers automatically when a drop-down list value is changed, moving the row to the target sheet and deleting the original row.

This method utilizes the Worksheet_Change event built into Excel's VBA to constantly monitor a specific column containing your drop-down lists (e.g., Column I). When a value in this column is modified, the script immediately copies the relevant data range to the designated sheet and removes the original row.

Open the Visual Basic Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Locate the Source Worksheet Module

In the Project Explorer pane on the left, find the name of your source worksheet (e.g., 'Register'). Double-click it to open its specific code module. Do not insert a standard module.

Add the Worksheet_Change Code

Paste a Worksheet_Change event macro into the code window. Ensure the code is set to monitor your drop-down column (like Column I) and copies columns A:M to the worksheet named in the drop-down cell.

Test the Automation

Close the VBA editor. Go back to your 'Register' sheet, select a value from the drop-down list, and verify that the row disappears from the current sheet and appears on the newly selected sheet.

Use a VBA Worksheet_Change Event to Automate Row Moving

Enable Macros: For this automated event to function, you must enable macros in your Excel Trust Center or WPS Spreadsheet security settings.

Advanced Macro Support

Automate Data Movement with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to easily automate repetitive tasks like moving rows based on drop-down selections. Enjoy a familiar interface that handles advanced data operations smoothly.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet file.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic Editor', or simply press Alt + F11.
3. Apply your VBA Code: Double-click your source sheet in the project explorer and paste your Worksheet_Change VBA code to automate the row transfer.
4. Save as Macro-Enabled: Go to Menu > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to keep your automated scripts active.

Full compatibility with Microsoft Excel VBA macros and scriptsSupports saving and executing .xlsm Macro-Enabled file formats nativelyBuilt-in data validation tools for creating flawless drop-down listsLightweight application with high performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA code not triggering when I change the drop-down list?

This usually happens if macros are disabled in your security settings, or if the VBA code was accidentally placed in a standard Module instead of the specific Worksheet object module (e.g., 'Sheet1') where the drop-down exists.

What happens if the drop-down text doesn't match the worksheet name?

The VBA macro will typically throw a 'Subscript out of range' (Error 9) because it cannot find a destination worksheet with that exact name. Ensure all drop-down options match your sheet names perfectly, avoiding any trailing or extra spaces.

Can I modify the VBA code to only copy the row instead of moving it?

Yes. You can remove the specific line of code that deletes the source row (usually written as 'Target.EntireRow.Delete' or similar). This ensures the data is copied to the new sheet while remaining completely intact on the original register.

How do I create a drop-down list for this VBA macro to use?

Select the cells where you want the drop-down to appear, go to the 'Data' tab, and click 'Data Validation'. Under the 'Allow' criteria, choose 'List', and input your worksheet names separated by commas in the 'Source' box.

Excel & Data Problems