How to get sure that i just copy & paste values and no format?-CodePudding

Good morning,

I tried with ActiveCell.PasteSpecial Paste:=xlPasteValuesbut it doesnt work.

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

Thanks

Best regards

CodePudding user response：

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

CodePudding user response：

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.