Move row to another sheet based on cell value - values only

[Drexl88]

Hi

I found the below code to move rows to another sheet when column D entry = "Leaver", however, it also transfers with it conditional formatting and formulas etc. Is there a way to transfer just the values but still delete the full row from the original. Any help would be much appreciated.

Sub Leaver()
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Mandatory Training").UsedRange.Rows.Count
    J = Worksheets("Mandatory Training Leavers").UsedRange.Rows.Count
    If J = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Mandatory Training Leavers").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Mandatory Training").Range("D1:D" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "Leaver" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Mandatory Training Leavers").Range("A" & J + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "Leaver" Then
                K = K - 1
            End If
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Try it like

   xRg(k).EntireRow.Copy
   Worksheets("Mandatory Training Leavers").Range("A" & j + 1).PasteSpecial xlPasteValues
   xRg(k).EntireRow.Delete

 

[Drexl88]

great - thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Drexl88]

Hi fluff, I am getting a bug when running this (image attached). Works fine until I make the paste values change. Can you help?

Thanks

 

[Fluff]

What is the error message & number?

 

[Drexl88]

Ignore me - had applied it to a different sheet that was protected - issue resolved by adding ActiveSheet.Unprotect Password:/ActiveSheet.Protect Password:

thanks again

 

[Fluff]

Glad you sorted it & thanks for letting us know.