Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste only Values, no Formatting
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''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste only Values, no Formatting
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''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste only Values, no Formatting
Try
i.EntireRow.Copy Dest.PasteSpecial Paste:=xlPasteValues Set Dest = Dest.Offset(1) If this post helps click Yes --------------- Jacob Skaria "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''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste only Values, no Formatting
That did it! Thanks to both of you.
Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Try i.EntireRow.Copy Dest.PasteSpecial Paste:=xlPasteValues Set Dest = Dest.Offset(1) If this post helps click Yes --------------- Jacob Skaria "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''. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste only Values, no Formatting
Your welcome and thanks for the feedback
"ryguy7272" wrote: That did it! Thanks to both of you. Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: Try i.EntireRow.Copy Dest.PasteSpecial Paste:=xlPasteValues Set Dest = Dest.Offset(1) If this post helps click Yes --------------- Jacob Skaria "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''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro copy data based on cell formatting and paste special values | Excel Programming | |||
Find matching values, copy/paste values as well as values in ColA | Excel Programming | |||
Copy and paste formatting and values only | Excel Programming | |||
Copy and paste values, formatting and formulas | Excel Discussion (Misc queries) | |||
Paste Special Values but keep formatting and column widths | Excel Programming |