Hi,

Like this

Sub newone()
Dim RngCol As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngCol = Range("A1", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A1")
End With

For Each i In RngCol
If i.Value = "x" Then
i.EntireRow.Copy
Dest.PasteSpecial xlPasteValues
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

Mike

"ryguy7272" wrote:

I am wondering how to change this code to paste only values, no formatting:

Sub newone()
Dim RngCol As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngCol = Range("A1", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A1")
End With
For Each i In RngCol
If i.Value = "x" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

I tried .Value and PasteSpecial Paste:=xlValues, but can't seem to get the
combination right. what am I missing?

TIA,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.