excel - 工作表更改事件未触发？

Question

我在工作表对象中有代码，以便触发格式和公式粘贴到下一个 lr .

Private Sub Worksheet_Change(ByVal target As Range)
    Dim lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).row
    If Intersect(target, Range("D1:D" & lr)) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Rows(lr).Copy
    Rows(lr).EntireRow.PasteSpecial Paste:=xlPasteFormats
    Rows(lr).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub

由于这是在附加下一个新行的数据时触发的，所以这个触发器不应该是它自己的吗？

Answer 1

工作表更改:SpecialCells 壮举。向下填充

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const Cols As String = "A:BC"
    
    Dim lCell As Range
    Set lCell = Columns(Cols).Find("*", , xlFormulas, , xlByRows, xlPrevious)
    
    If lCell Is Nothing Then Exit Sub
    If Intersect(Columns(Cols).Rows(lCell.Row), Target) Is Nothing Then Exit Sub
    
    On Error GoTo clearError
    
    Dim rrg As Range: Set rrg = Rows(lCell.Row - 1)
    Application.EnableEvents = False
    
    Dim frg As Range: Set frg = rrg.SpecialCells(xlCellTypeFormulas)
    Intersect(rrg.Resize(2), frg.EntireColumn).FillDown
    
    rrg.Copy
    rrg.Offset(1).PasteSpecial xlPasteFormats
    
    rrg.Cells(1).Offset(1).Select

SafeExit:
    If Application.CutCopyMode Then
        Application.CutCopyMode = False
    End If
    If Not Application.EnableEvents Then
        Application.EnableEvents = True
    End If
    
    Exit Sub

clearError:
    Debug.Print "Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    Resume SafeExit

End Sub