Paste only Values, no Formatting

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

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

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

If this post helps click Yes
 
Back
Top