Copy Paste Values If Value = X

Question

I'm trying to write a code that moves a row from one sheet to another when a value in one the columns is changed. For testing purposes, it reads that if the cell in column 13 reads "Test", then that row is deleted from the original sheet and moved into the new sheet, "TestSheet". However, I'm trying to get the row thats moved into the new one to be pasted as values, but when I try to include the Pastespecial, it shows errors.

This is my code as it stands

Sub move_rows_to_another_sheet_master()
For Each myCell In Selection.Columns(13).Cells
If myCell.Value = "Test" Then
myCell.EntireRow.Copy Worksheets("TestSheet").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next
End Sub

I'm pretty sure I need to include "PasteSpecial Paste:=xlValues" somewhere, but everywhere I try to include it, it seems to not work.

Answer 1

EDIT Asof @BigBen comment rephrased the answer.

The faulty code is that if you want to copy the entire row, but the target is not cover an entire row. Therefore try this:

myCell.EntireRow.Copy Worksheets("TestSheet").Range("B" & Rows.Count).End(xlUp)(2).EntireRow

If you want to paste in the first empty row use the Offset

myCell.EntireRow.Copy Worksheets("TestSheet").Range("A" & Rows.Count).End(xlUp).Offset(1)

If in the original range there are formulas and you need only the values either use only absolute references, or cannot directly copy/paste the range. Then as you solved has to use PasteSpecial

myCell.EntireRow.Copy
Worksheets("TestSheet").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial(xlPasteValues)

Answer 2

Try this code:

Sub move_rows_to_another_sheet_master()
   Dim myCell As Range, ToDelete As Range
   Set ToDelete = Range("A" & Rows.Count)  ' initialize ToDelete
   For Each myCell In Selection.Columns(13).Cells
      If myCell.Value = "Test" Then
         Worksheets("TestSheet").Range("A" & Rows.Count).End(xlUp)(2).EntireRow.Value = myCell.EntireRow.Value
         Set ToDelete = Union(ToDelete, myCell)
      End If
   Next myCell
   ToDelete.EntireRow.Delete
End Sub

There are 2 problems with your code.

1. If you want to copy values it's better to set destination values than to copy and paste.
2. If your For Each loop is from the beginning to the end and you delete rows during this loop you miss rows. Typically we should do it in the reverse order. But you need to have your copied rows without reversing.
   So you can save cells to be deleted in a separate range and afterwards delete all rows at once.