Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Notice that the generated code selects cell A1, copies it, and then selects cell B1 and performs the
paste operation. But in VBA, you don’t need to select an object to work with it. You would never
learn this important point by mimicking the preceding recorded macro code, where two
statements incorporate the Select method. You can replace this procedure with the following much
simpler routine, which doesn’t select any cells. It also takes advantage of the fact that the Copy
method can use an argument that represents the destination for the copied range.
Sub CopyRange()
Range(“A1”).Copy Range(“B1”)
End Sub
Both of these macros assume that a worksheet is active and that the operation takes place on the
active worksheet. To copy a range to a different worksheet or workbook, simply qualify the range
reference for the destination. The following example copies a range from Sheet1 in File1.
xlsx to Sheet2 in File2.xlsx . Because the references are fully qualified, this example works
regardless of which workbook is active.
Sub CopyRange2()
Workbooks(“File1.xlsx”).Sheets(“Sheet1”).Range(“A1”).Copy _
Workbooks(“File2.xlsx”).Sheets(“Sheet2”).Range(“A1”)
End Sub
Another way to approach this task is to use object variables to represent the ranges, as shown in
the code that follows:
Sub CopyRange3()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Workbooks(“File1.xlsx”).Sheets(“Sheet1”).Range(“A1”)
Set Rng2 = Workbooks(“File2.xlsx”).Sheets(“Sheet2”).Range(“A1”)
Rng1.Copy Rng2
End Sub
As you might expect, copying isn’t limited to one single cell at a time. The following procedure,
for example, copies a large range. Notice that the destination consists of only a single cell (which
represents the upper-left cell for the destination). Using a single cell for the destination works
just like it does when you copy and paste a range manually in Excel.
Sub CopyRange4()
Range(“A1:C800”).Copy Range(“D1”)
End Sub
 
Search JabSto ::




Custom Search