How to copy & paste values and no format?

Question

I tried ActiveCell.PasteSpecial Paste:=xlPasteValues.

Sub CopyCoverage()
    
    Dim x As Worksheet, y As Worksheet, LastRow
    
    Set x = Sheets("1SalesAnalysis")
    Set y = Sheets("Basics")
    
    LastRow = x.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    x.Range("A2:A" & LastRow).Copy y.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
    x.Range("B2:B" & LastRow).Copy y.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
    x.Range("C2:C" & LastRow).Copy y.Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
    x.Range("D2:D" & LastRow).Copy y.Cells(Rows.Count, "L").End(xlUp).Offset(1, 0)
    
    x.Range("E2:E" & LastRow).Copy y.Cells(Rows.Count, "M").End(xlUp).Offset(1, 0)
    x.Range("F2:F" & LastRow).Copy y.Cells(Rows.Count, "P").End(xlUp).Offset(1, 0)
    x.Range("G2:G" & LastRow).Copy y.Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0)
    x.Range("H2:H" & LastRow).Copy y.Cells(Rows.Count, "R").End(xlUp).Offset(1, 0)
    
    x.Range("I2:I" & LastRow).Copy y.Cells(Rows.Count, "S").End(xlUp).Offset(1, 0)
    x.Range("J2:J" & LastRow).Copy y.Cells(Rows.Count, "T").End(xlUp).Offset(1, 0)
    x.Range("K2:K" & LastRow).Copy y.Cells(Rows.Count, "V").End(xlUp).Offset(1, 0)
    x.Range("L2:L" & LastRow).Copy y.Cells(Rows.Count, "W").End(xlUp).Offset(1, 0)
    
    x.Range("O2:O" & LastRow).Copy y.Cells(Rows.Count, "EA").End(xlUp).Offset(1, 0)
    x.Range("P2:P" & LastRow).Copy y.Cells(Rows.Count, "EI").End(xlUp).Offset(1, 0)
    x.Range("Q2:Q" & LastRow).Copy y.Cells(Rows.Count, "EB").End(xlUp).Offset(1, 0)
    
    x.Range("R2:R" & LastRow).Copy y.Cells(Rows.Count, "EJ").End(xlUp).Offset(1, 0)
    x.Range("S2:S" & LastRow).Copy y.Cells(Rows.Count, "EC").End(xlUp).Offset(1, 0)
    x.Range("T2:T" & LastRow).Copy y.Cells(Rows.Count, "EK").End(xlUp).Offset(1, 0)
    
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
End Sub

Pastevalues works for me. Show what you copy and the result. Or is the destination already formatted? — Solar Mike, Oct 26 '21 at 07:17

score 1 · Answer 1 · answered Oct 26 '21 at 08:32

1

The best way to avoid formatting not being copied/pasted is by not copying/pasting in the first place: you can simply do:

Destination_Range.Value = Source_Range.Value

Like this, only the value gets copied", but the formatting is not involved.

More information can be found in this reference question about this subject.

answered Oct 26 '21 at 08:32

Dominique

16,450
15
56
112

but isnt the problem that i cant add .value to equation on the right? Because .value does not refer to a Range x.Range("A2:A" & LastRow).Cells.Value = y.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Cells.Value – Sven Oct 26 '21 at 10:19
1

@Sven: I believe you'll need a `For Each` loop to copy all the values in your range. – Dominique Oct 26 '21 at 11:08

score 1 · Accepted Answer · answered Oct 26 '21 at 12:50

Store the mapping rules in an array so you can reuse the same code for each column.

Option Explicit

Sub CopyCoverage()

    Dim wsX As Worksheet, wsY As Worksheet
    Dim LastRowX As Long, msg As String
    Dim rngX As Range, rngY As Range
    
    Set wsX = Sheets("1SalesAnalysis")
    Set wsY = Sheets("Basics")
    LastRowX = wsX.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    Dim map, ar, i As Integer
    map = Split("A=>E,B=>F,C=>G,D=>L,E=>M,F=>P,G=>Q,H=>R,I=>S,J=>T,K=>V,L=>W," & _
                "O=>EA,P=>EI,Q=>EB,R=>EJ,S=>EC,T=>EK", ",")
    
    Application.ScreenUpdating = False
    For i = 0 To UBound(map)
        ar = Split(map(i), "=>")
        msg = msg & vbLf & ar(0) & " to " & ar(1)

        Set rngX = wsX.Range(ar(0) & "2:" & ar(0) & LastRowX)
        Set rngY = wsY.Cells(Rows.Count, ar(1)).End(xlUp).Offset(1, 0)
        rngY.Resize(rngX.Rows.Count).Value2 = rngX.Value2
    Next
    Application.ScreenUpdating = True
    MsgBox "Copied " & msg, vbInformation

End Sub

score 0 · Answer 3 · answered Oct 26 '21 at 07:20

Those one-line 'copy-pastes' already finishes the task of copy-paste, so the ActiveCell.PasteSpecial at the bottom part of your code doesn't do anything.

There are several ways to do it but I will stick to the pattern of your code:

Sub CopyCoverage()
    
    Dim x           As Worksheet
    Dim y           As Worksheet
    Dim LastRow     As Long
    
    Set x = ThisWorkbook.Sheets("Sheet2")
    Set y = ThisWorkbook.Sheets("Ans")

    LastRow = x.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    Application.ScreenUpdating = False '~turn off the 'animation' to speed up a bit
    
    'The logic will be, copy-paste, copy-paste
    x.Range("A2:A" & LastRow).Copy
    y.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    
    x.Range("B2:B" & LastRow).Copy
    y.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    
    'and so and so forth
    'Just continue with this pattern
    
    Application.CutCopyMode = False '~end line
    Application.ScreenUpdating = True '~turn on the 'animation' again
    
End Sub

thanks @ChaosPH but it submits me an error message ..it expects an intorduction end — Sven, Oct 26 '21 at 09:55

How to copy & paste values and no format?

3 Answers3