首页 文章

VBA - 将公式复制并粘贴为插入行的值

提问于
浏览 次
0

Excel 2016 - 当用户在D列中输入“CRED”时，需要Excel复制整行并插入下一行 .

下面的代码插入下一行AOK，包括RECMER的新代码（D列） . 除此之外，我还需要将公式（C列）复制/粘贴为下一行同一列中的值 .

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHnd

'Don't do anything if more than one cell has been changed
If Target.Cells.Count > 1 Then Exit Sub

'Determine if the changed cell is in Column C and is a Y
If Target.Column = 4 Then
    If Target = "CRED" Then
        'Disable events so code doesn't fire again when row is inserted
        Application.EnableEvents = False

        'Copy & Insert changed Row, Clear dotted lines
        Target.EntireRow.Copy
        Range("A" & Target.Row + 1).Insert Shift:=xlDown
        Application.CutCopyMode = False

        'Put 2201 in Column B of inserted Row
        Range("D" & Target.Row + 1) = "RECMER"
    End If
End If

errHnd:
'Re-enable event
Application.EnableEvents = True

End Sub
excel-vba vba excel

1 回答

  • 0

    请尝试以下代码：

    Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHnd

'Don't do anything if more than one cell has been changed
If Target.Cells.Count > 1 Then Exit Sub

'Determine if the changed cell is in Column C and is a Y
If Target.Column = 4 Then
    If Target = "CRED" Then
        'Disable events so code doesn't fire again when row is inserted
        Application.EnableEvents = False

        'Copy & Insert changed Row, Clear dotted lines
        Target.EntireRow.Copy
        Range("A" & Target.Row + 1).Insert Shift:=xlDown
        Application.CutCopyMode = False

        'Put 2201 in Column B of inserted Row
        Range("D" & Target.Row + 1) = "RECMER"

        ' ======= Added the 2 lines of code below =======
        ' copy >> paste special values only from Column C
        Range("C" & Target.Row).Copy
        Range("C" & Target.Row + 1).PasteSpecial xlPasteValues
    End If
End If

errHnd:
'Re-enable event
Application.EnableEvents = True

End Sub
    回复于

相关问题