CODEWITHSUNDEEP
vbaselectcopyformatpaste
Nikhen
Nikhen

Reputation: 13

How to copy paste all formats AND values without using select in vba

How can I copy a range without using select. I need everything, not only the values. Current bulky code:

Function Template(x As Integer)
    Sheets("Sheet1").Select
    Sheets("Sheet1").Range("E1:H5").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Sheets("Sheet2").Cells(1, x).Select
    ActiveSheet.Paste
End Function

Thanks!!

Upvotes: 1

Views: 12340

Answers (2)

3-14159265358979323846264
3-14159265358979323846264

Reputation: 3290

You can use Range.Copy (https://msdn.microsoft.com/en-us/library/office/ff837760.aspx) to copy a range without the selection occurring, for instance ...

Range("A1").Copy Range("A2")

will copy everything including values and formats from A1 to A2.

Upvotes: 2

mielk
mielk

Reputation: 3940

Function Template(x As Integer)
    Dim sourceRange As Excel.Range
    Dim destinationRange As Excel.Range

    Set sourceRange = Sheets("Sheet1").Range("E1:H5")
    Call sourceRange.Copy


    Set destinationRange = Sheets("Sheet2").Cells(1, x)
    Call destinationRange.PasteSpecial(xlPasteAll)

End Function

Upvotes: 0

Related Questions