Help with paste values

[foamcows]

Hi all,

I am using the formula below to search through my data, and copy all the rows that have the term "cleared" in column Z to a new workbook called closed.

I am having issues with the current formula not pasting values. I am sure it is an easy fix, but I need the formula to paste the values instead of the original cells formulas.

Right now, everything works, but it I just need the values.

Here is the code:

For datarow = 3 To lastrow
    If InStr(1, FromSheet.Cells(datarow, "z"), "Cleared", vbTextCompare) > 0 Then
        FromSheet.Cells(datarow, "A").EntireRow.Copy _
        Destination:=Workbooks("Closed.xls").Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next datarow

If anyone could, please help.

[Domski]

Try:

For datarow = 3 To lastrow
    If InStr(1, FromSheet.Cells(datarow, "z"), "Cleared", vbTextCompare) > 0 Then
        FromSheet.Cells(datarow, "A").EntireRow.Copy
        Workbooks("Closed.xls").Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp) _
            .Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next datarow

Dom

[foamcows]

That works perfect, thank you very much.