Search code examples
excelvbacopy-paste

Copy Paste Values If Value = X

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.

Solution

  • 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)