VBA & Macro Problems

How to Move an Excel Row to Another Sheet When Cell is YES

Natalie Taylor Oct 9, 2026 869 views

Question details

The user needs to automate moving a row to a destination worksheet when a cell in Column F changes to YES, but is encountering a VBA Ambiguous Name error.

How to Move an Excel Row to Another Sheet When Column F is YES

Product: Excel
Device & OS: not provided
Scenario: Automating data management by transferring completed records to another sheet using a VBA macro without triggering an infinite loop or error.
Observed behavior: An 'Ambiguous Name Detected: Worksheet_Change' error occurs whenever any cell is changed, preventing the row from moving because there are duplicate event macros in the same module.

Before you start

Before modifying your VBA code, save a backup copy of your macro-enabled workbook and ensure that you have access to the Developer tab to edit the module.

Solution 1Recommended

Combine Duplicate Worksheet_Change Procedures

Resolves the 'Ambiguous Name Detected' error by merging the row-moving logic and any existing data validation code into one unified event macro.

VBA strictly allows only one Worksheet_Change event per worksheet module. If you have separate scripts for data validation and for moving rows, the system cannot determine which one to run, resulting in the Ambiguous Name error.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) window. In the Project Explorer pane on the left, double-click the specific worksheet where you are entering the data.

Locate Duplicate Procedures

Scroll through the code window to find any duplicate routines named 'Private Sub Worksheet_Change(ByVal Target As Range)'.

Merge the Code Logic

Combine the codes into a single Worksheet_Change block. Use an If statement like 'If Target.Column = 6 And UCase(Target.Value) = "YES" Then' to specify that the action only triggers for Column F.

Disable Events to Prevent Loops

Inside your If statement, add 'Application.EnableEvents = False' before copying the row. This is crucial because deleting or moving the row will otherwise trigger the Worksheet_Change event again.

Copy, Delete, and Re-enable Events

Add the code to copy the target row to the next empty row on your destination sheet (e.g., 'Completed Service Orders'), delete the original row, and finally use 'Application.EnableEvents = True' to restore normal functionality.

Combine Duplicate Worksheet_Change Procedures

Best Practice: Always ensure Application.EnableEvents = True is placed at the end of your script and within an error handler, so events are restored even if the macro fails midway.

Solution 2

Remove Redundant VBA Code

If the secondary Worksheet_Change macro is no longer needed, simply deleting it will immediately fix the Ambiguous Name error.

Advanced Spreadsheet Automation

Automate Your Spreadsheets with WPS Office

WPS Office provides robust support for VBA and macros, allowing you to seamlessly run complex scripts like moving rows based on cell values without compatibility issues.

1. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click the 'VBA Editor' icon.
3. Insert the Unified Code: Double-click your target worksheet in the Project Explorer and paste your unified Worksheet_Change macro.
4. Test the Automation: Save the file, return to the spreadsheet, and type 'YES' in Column F to watch the row automatically move to the destination sheet.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) formats.Built-in support for VBA macros and advanced spreadsheet functions.Lightweight software that runs scripts quickly and efficiently.Free to download and use for your daily office needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get the 'Ambiguous Name Detected' error in Excel VBA?

This error occurs when there are two or more macros or procedures with the exact same name (such as Worksheet_Change) within the same VBA module. VBA requires unique names for each procedure to know which one to execute.

How do I find the next available row on my destination worksheet using VBA?

You can find the next empty row dynamically by using a code snippet like `NextRow = Sheets("DestinationSheet").Cells(Rows.Count, 1).End(xlUp).Row + 1`. This ensures your moved data is pasted immediately below existing records without overwriting them.

Why does Excel freeze or crash when my macro moves a row?

If you don't disable events using `Application.EnableEvents = False` before cutting or deleting the row, the change triggers the macro again, causing an infinite loop. Always remember to re-enable events with `Application.EnableEvents = True` at the end of the script.

Excel & Data Problems