请求完善Excel VBA代码：选中"Did not attend"时插入重复行

Got it, let's tweak your VBA code to add that duplicate row insertion in the Details sheet whenever "Did not attend" is selected from the dropdown. Here's the updated version that retains your existing functionality and adds the new feature:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if a single cell in column E was modified
    If Target.Column = 5 And Target.Cells.Count = 1 Then
        ' Check if "Did not attend" was selected
        If Target.Value = "Did not attend" Then
            ' Disable events to prevent infinite loops
            Application.EnableEvents = False
            
            ' Insert a new row below the current row
            Target.EntireRow.Offset(1).Insert
            
            ' Copy the original row's content to the newly inserted row
            Target.EntireRow.Copy
            Target.EntireRow.Offset(1).PasteSpecial xlPasteAll
            
            ' Calculate the next empty row in "Non Attendance" sheet
            Dim nxtRw As Long
            nxtRw = Sheets("Non Attendance").Cells(Sheets("Non Attendance").Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy the original row to "Non Attendance" sheet
            Target.EntireRow.Copy Destination:=Sheets("Non Attendance").Range("A" & nxtRw)
            
            ' Clear clipboard to avoid lingering copy selection
            Application.CutCopyMode = False
            
            ' Re-enable events
            Application.EnableEvents = True
        End If
    End If
End Sub

Key Changes & Explanations: #

• Fixed the event procedure name: Changed Worksheet_Change1 to Worksheet_Change — this is the mandatory name for the worksheet change event to trigger automatically when a cell is edited.
• Added duplicate row creation: After inserting the new row below the target row, we copy all content (values, formatting, formulas) from the original row to the new one using xlPasteAll.
• Self-contained row calculation: Added explicit code to find the next empty row in the Non Attendance sheet, so you don't have to rely on an external nxtRw variable definition.
• Cleaned up the clipboard: Added Application.CutCopyMode = False to remove the persistent copy selection indicator after the operation finishes.

Quick Notes: #

• Ensure this code is pasted into the Details sheet's code module (right-click the sheet tab > "View Code" > paste the code there).
• If you already have the nxtRw variable defined elsewhere in your project, you can remove the Dim nxtRw As Long line and the calculation line to use your existing variable.

内容的提问来源于stack exchange，提问作者jnbentley training