Friday, July 25, 2014

Excel Macro VBA - Copy Paste Without Selecting A Range

How do we copy and paste a range without selecting it?

How do we significantly reduce the lines of code to perform a copy and paste?

Consider the following example:

Sheet 1:

















Sheet 2:
















I need to copy the cells from A1 to C5 in Sheet 1 to B2 to D6 in Sheet 2.

It is a common practice to write the following in Excel VBA to perform a copy - paste of above requirement.

Sub CopySteps()

    Range("A1:C5").Select

    Selection.Copy

    Sheets("Sheet2").Select

    Range("B2").Select

    ActiveSheet.Paste

End Sub

It is also possible to copy and paste a range efficiently in a single line of code without selecting the range.

Sub CopySteps()

    Range("A1:C5").Copy Sheets("Sheet2").Range("B2")

End Sub

The output of above routine is given below:





















Let me know if you have a better way to handle it.

Thanks.

No comments:

Post a Comment