VBA中重命名Sub以复用、行移动相关技术问题

Hey Perry, let's break down what's going wrong and fix this step by step—you're already on the right track, just a couple of key missteps with the event handler and logic.

First: Why Renaming Worksheet_Change Breaks Everything #

Excel's worksheet event handlers have fixed, required names—Worksheet_Change is the only name Excel recognizes for the "cell value changed" trigger. Renaming it to Worksheet_ChangeCOMPLETE or anything else means Excel has no idea it should run that code when a cell changes. So we need to stick with the standard name, and add logic inside the procedure to target only the "completed order" actions.

Here's a Complete, Tested Solution #

Let's assume:

• Your orders are on a sheet named Orders in your main workbook
• You mark an order as completed by entering "Completed" in column D (adjust this to match your actual status column)
• The target workbook is named COMPLETED.xlsx (make sure it's saved in the same folder as your main workbook, or update the file path below)

Paste this code into the code module for your Orders worksheet (right-click the sheet tab > View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Disable events to prevent infinite loops when we delete the row
    Application.EnableEvents = False
    
    ' Use error handling to make sure events get re-enabled even if something breaks
    On Error GoTo Cleanup
    
    ' Define the range that triggers the action: column D (status column)
    Dim StatusCol As Range
    Set StatusCol = Me.Range("D:D")
    
    ' Check if the changed cell is in the status column
    If Not Intersect(Target, StatusCol) Is Nothing Then
        ' Check if the cell value is "Completed" (case-insensitive)
        If UCase(Target.Value) = "COMPLETED" Then
            Dim CompletedWB As Workbook
            Dim TargetSheet As Worksheet
            Dim NextEmptyRow As Long
            
            ' Try to open the COMPLETED workbook, or activate it if it's already open
            On Error Resume Next
            Set CompletedWB = Workbooks("COMPLETED.xlsx")
            On Error GoTo Cleanup
            
            ' If the workbook isn't open, open it (update the file path if needed)
            If CompletedWB Is Nothing Then
                Set CompletedWB = Workbooks.Open(ThisWorkbook.Path & "\COMPLETED.xlsx")
            End If
            
            ' Use the first sheet in the COMPLETED workbook (adjust sheet name if needed)
            Set TargetSheet = CompletedWB.Sheets(1)
            
            ' Find the next empty row in the target sheet
            NextEmptyRow = TargetSheet.Cells(TargetSheet.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy the entire row to the target workbook
            Target.EntireRow.Copy Destination:=TargetSheet.Cells(NextEmptyRow, "A")
            
            ' Delete the original row from your orders sheet
            Target.EntireRow.Delete Shift:=xlUp
        End If
    End If

Cleanup:
    ' Re-enable events no matter what happens
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        MsgBox "An error occurred: " & Err.Description, vbExclamation
    End If
End Sub

Key Notes for You (As a New VBA Developer) #

• Don't rename Worksheet_Change: This is a non-negotiable—Excel only triggers procedures with the exact event name.
• Error handling is critical: The Cleanup section ensures that even if something goes wrong (like the COMPLETED workbook is missing), Excel doesn't get stuck with events disabled (which would break all future change triggers).
• Adjust the details: Change the status column (D), sheet names, and workbook path to match your actual setup.
• Test incrementally: First test if the code triggers when you enter "Completed" in the status column, then check if the row copies, then if it deletes. This helps you spot any small issues.

Troubleshooting Common Issues #

• If the code doesn't run at all: Make sure you pasted it into the worksheet module, not a standard module. Right-click the sheet tab > View Code to get there.
• If the COMPLETED workbook doesn't open: Double-check the file path—ThisWorkbook.Path uses the folder where your main workbook is saved. If COMPLETED is elsewhere, replace that with the full path (e.g., "C:\Documents\COMPLETED.xlsx").
• If rows don't delete: Ensure the status cell is exactly what you're checking for (we used UCase to make it case-insensitive, so "completed", "Completed", or "COMPLETED" all work).

内容的提问来源于stack exchange，提问作者Perry Kendrick