Paste only Values, no Formatting

[ryguy7272]

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

 

[Mike H]

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

 

[Jacob Skaria]

Try

i.EntireRow.Copy
Dest.PasteSpecial Paste:=xlPasteValues
Set Dest = Dest.Offset(1)

If this post helps click Yes

 

[ryguy7272]

That did it! Thanks to both of you.
Ryan---

 

[Mike H]

Your welcome and thanks for the feedback

That did it! Thanks to both of you.
Ryan---