Paste copied values with .PasteSpecial

Question

Please help me for this problem:

I use this vba from this link:

Sub test()
 Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer
    For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("Sheet2").Range("B" & i)
        For j = 1 To Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("Sheet1").Range("C" & j)
            Set rngName = Sheets("Sheet1").Range("B" & j)
            If rng1.Value = rng2.Value Then
                rngName.Copy Destination:=Worksheets("Sheet2").Range("E" & i)
            End If

        Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
 Next i
End Sub

Can somebody show me how to combine rngName.Copy with

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

I would like to earn, that the rngName.Copy copy only text, because I have special color, text format, comment etc. in the cells, where the vba paste the changed values and I would like to stay these.

Answer 1

Do you mean this?

rngName.Copy
Worksheets("Sheet2").Range("E" & i).PasteSpecial Paste:=xlPasteFormulas, _
  Operation:=xlNone, SkipBlanks:=False, Transpose:=False

You may also want xlPasteValues instead of xlPasteFormulas. In this case, a simpler and better way is to take the value without using copy/paste:

Worksheets("Sheet2").Range("E" & i).Value = rngName.Value

All of the methods above conserve the formatting of the destination.