How To Add PasteValues To PasteDestination VBA Code?

Question

I want to add a line of code to copy only the values of column C with no formatting.

Part of my code currently looks like this:

If Worksheets("Sheet1").Cells(i, 4).Value = FISH Then
    Worksheets("Sheet1").Cells(i, 3).Copy
    erow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet1").Paste Destination:=Worksheets("Sheet2").Cells(erow + 1, 1)

I found a simple PasteValues method online which states the following:

Range("A1:A3").Copy
Range("B1:B3").pastespecial xlPasteValues

However what I actually want is to add the PasteValues into my existing code.

Does anyone know how I could achieve this?

Answer 1

If you only want the values then skip the clipboard and assign the values directly:

If Worksheets("Sheet1").Cells(i, 4).Value = FISH Then
    erow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet2").Cells(erow + 1, 1).Value = Worksheets("Sheet1").Cells(i, 3).Value
End If

---

If you are determined to use the clipboard then:

If Worksheets("Sheet1").Cells(i, 4).Value = FISH Then
    Worksheets("Sheet1").Cells(i, 3).Copy
    erow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet2").Cells(erow + 1, 1).PasteSpecial xlPasteValues
End If

But this will be slower, especially in a loop.