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

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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.

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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
   xRg(k).EntireRow.Copy
   Worksheets("Mandatory Training Leavers").Range("A" & j + 1).PasteSpecial xlPasteValues
   xRg(k).EntireRow.Delete
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Attachments

  • Bug.png
    Bug.png
    143.4 KB · Views: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
What is the error message & number?
 

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for letting us know.
 

Forum statistics

Threads
1,140,917
Messages
5,703,168
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top