If, Then, With Paste Only Values

Question

I have code that works perfectly except for I can not seem to paste only values of the copied cell. How would I tell this code to only paste values and not merely copy and paste the cell. I have tried Google, Stack etc with no luck every variation of paste special I try fails! Thanks!

Sheets("AAAA").Select

Dim LR As Long, i As Long
With Sheets("AAAA")
    LR = .Range("H" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
       With .Range("G" & i)
            If .Cells.Value = "NO MATCH" Then
                With Sheets("BBBB")
                    ActiveSheet.Cells(i, 1).Copy .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
                    ActiveSheet.Cells(i, 2).Copy .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0)
                    ActiveSheet.Cells(i, 3).Copy .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0)
                    ActiveSheet.Cells(i, 4).Copy .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0)
                    ActiveSheet.Cells(i, 5).Copy .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0)
                    ActiveSheet.Cells(i, 7).Copy .Cells(.Rows.Count, "H").End(xlUp).Offset(1, 0)
                    ActiveSheet.Cells(i, 8).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
                End With
            End If
        End With
    Next i
End With

Havbe you tried googling "paste values"? (or checking this site) — ashleedawg, May 04 '18 at 23:03
No need to down vote... as I said I have googled and searched for the proper syntax to paste values only in my code. I have also seen this linked question which covers basic paste values code, as I said I have searched for a solution. Thanks! — Andrew Caprario, May 04 '18 at 23:08
Did you try the `PasteSpecial` method? Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False https://stackoverflow.com/a/30374158/194717 — Tony, May 04 '18 at 23:12
You could also consider assigning the values directly, instead of using clipboard: `range("A1").value2 = range("B1").value2` assigns whatever value is in cell B1 to A1 (remember to qualify workbook and worksheet name). — chillin, May 04 '18 at 23:13

QHarr · Accepted Answer · 2018-05-05T05:06:16.510

1

Be careful with nested With statements. Maybe fully qualify the second sheet in the inner With.

That said, you can remove the copy altogether and swop it round into an assignment using .Text property of range (cell) to give value only e.g.

 .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = ActiveSheet.Cells(i, 1).Text

Better still use .Value2

.Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = ActiveSheet.Cells(i, 1).Value2

It preserves more formats and is more resilient.

edited May 05 '18 at 05:06

answered May 04 '18 at 23:15

QHarr

83,427
12
54
101

1

Thanks for this. I see my error in how I was writing this out. Thanks! – Andrew Caprario May 04 '18 at 23:29
Why use `.Text`? It's slower, limited to a single cell, and may give unexpected results depending on the cell format. – chris neilsen May 05 '18 at 03:42
@chrisneilsen Thanks. Wasn't thinking properly. – QHarr May 05 '18 at 05:06

If, Then, With Paste Only Values

1 Answers1