同一Excel工作簿内基于状态的跨工作表行自动移动需求

Hey there! Let's get this Excel automation set up for you—it's straightforward with a bit of VBA code, since built-in features can't handle this automatic row movement on their own. Here's a step-by-step solution:

If you don't see the Developer tab at the top of Excel:

• Go to File > Options > Customize Ribbon
• Check the box next to Developer in the right-hand pane, then click OK

• Switch to your worksheet1 tab
• Right-click the tab at the bottom of the window, then select View Code—this will open the VBA editor window tied directly to worksheet1

In the code window that pops up, paste this VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lastRow As Long
    Dim targetRow As Long
    
    ' Set references to our two worksheets
    Set ws1 = ThisWorkbook.Worksheets("worksheet1")
    Set ws2 = ThisWorkbook.Worksheets("worksheet2")
    
    ' Only react to changes in the Status column (adjust if your Status is in a different column)
    If Target.Column = ws1.Range("Status").Column Then
        ' Turn off event handling temporarily to avoid infinite loops
        Application.EnableEvents = False
        
        ' Check if the selected status is "In-Progress" (case-insensitive)
        If UCase(Target.Value) = "IN-PROGRESS" Then
            targetRow = Target.Row
            ' Find the next empty row in worksheet2
            lastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy the entire row to worksheet2
            ws1.Rows(targetRow).Copy Destination:=ws2.Rows(lastRow)
            
            ' Delete the original row from worksheet1 (shifts rows up automatically)
            ws1.Rows(targetRow).Delete Shift:=xlUp
        End If
        
        ' Turn event handling back on so future edits work
        Application.EnableEvents = True
    End If
End Sub

• Named Range for Flexibility: The code uses a named range called "Status" to identify the Status column. To set this up:
  1. Select your Status column header (or the entire column)
  2. Go to Formulas > Define Name
  3. Name it "Status" and click OK. If you prefer, you can replace ws1.Range("Status").Column with a hardcoded column number (like 2 if Status is column B)
• Save as Macro-Enabled File: Save your workbook as an .xlsm file (Macro-Enabled Workbook) to keep the code active
• Case Insensitivity: The UCase() function ensures the code works even if someone types "in-progress" or "In-Progress" instead of the exact dropdown text
• Avoids Loops: Application.EnableEvents = False prevents the code from triggering itself when we delete the row (which would count as a cell change)

Once you set this up, every time you select "In-Progress" from the Status dropdown in worksheet1, that entire row will automatically move to worksheet2, leaving only "Completed" rows in worksheet1.

内容的提问来源于stack exchange，提问作者Karun S