Worksheet Change Event to copy part of row to another sheet with values only

[Jim885]

I have the below code that works. But as you can see, it uses two lines of code to copy the range of the row that is changed. I need help to make it copy the split range of the row using a single line of code ... and also copy the values only. Currently, this code copies the formula.

Could someone please tweak it?
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dtmTime As Date
    Dim LR, LR2 As Long
    Dim myWorksheet As Worksheet
    Dim myWorksheetName As String
    myWorksheetName = Format(Now, "mmm yyyy")
    
    LR = Sheets(myWorksheetName).Range("A" & Rows.Count).End(xlUp).Row
    If Intersect(Target, Range("D2:E" & LR)) Is Nothing Then Exit Sub
    dtmTime = Now()
    
    LR2 = Sheets("Audit Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Audit Sheet")
        .Cells(LR2, 1) = dtmTime
        .Cells(LR2, 2) = Environ("USERNAME")
    End With
    
    'Need the below section to copy column A and columns C through F of the changed row onto the Audit sheet with a single line of code that also pastes values only
    Sheets(myWorksheetName).Range("A" & Target.Row).Copy Destination:=Sheets("Audit Sheet").Range("C" & LR2)
    Sheets(myWorksheetName).Range("C" & Target.Row & ":F" & Target.Row).Copy Destination:=Sheets("Audit Sheet").Range("D" & LR2)
    ThisWorkbook.Save
End Sub

 

[JLGWhiz]

Since you are pasting to noncontiguous ranges, the copy and paste action cannot be made as one statement. The code below will paste values only.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dtmTime As Date
    Dim LR, LR2 As Long
    Dim myWorksheet As Worksheet
    Dim myWorksheetName As String
    myWorksheetName = Format(Now, "mmm yyyy")    
    LR = Sheets(myWorksheetName).Range("A" & Rows.Count).End(xlUp).Row
    If Intersect(Target, Range("D2:E" & LR)) Is Nothing Then Exit Sub
    dtmTime = Now()    
    LR2 = Sheets("Audit Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Audit Sheet")
        .Cells(LR2, 1) = dtmTime
        .Cells(LR2, 2) = Environ("USERNAME")
    End With
    
    'Need the below section to copy column A and columns C through F of the changed row onto the Audit sheet with a single line of code that also pastes values only
    Sheets(myWorksheetName).Range("A" & Target.Row).Copy
    Sheets("Audit Sheet").Range("C" & LR2).PasteSpecial xlPasteValues
    Sheets(myWorksheetName).Range("C" & Target.Row & ":F" & Target.Row).Copy 
    Sheets("Audit Sheet").Range("D" & LR2).PasteSpecial xlPasteValues
    ThisWorkbook.Save
End Sub

 

[Jim885]

Thank you very much for the advice and correcting the code!

 

[JLGWhiz]

Thank you very much for the advice and correcting the code!

You're Welcome,
Regards, JLG