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.
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.
VBA Code:
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
Last edited by a moderator: