CODEWITHSUNDEEP
excelvbarange
Rhiannon
Rhiannon

Reputation: 93

Copy range to destination and return destination range

I would like to copy a range to a new location in such a way that it returns the new range, which I can then store in a variable for further processing.

I gather the usual way of copying cells is to use the Range.Copy method:

Dim rCopiedRange As Range, rRangeToCopy as Range, rStartLocation as Range
Set rRangeToCopy = ActiveWorkbook.Sheets(1).Range("C4:F10")
Set rStartLocation = ActiveWorkbook.Sheets(2).Range("A2")
rRangeToCopy.Copy rStartLocation

But since this is a method I can't set the result to a variable and 

Set rCopiedRange = rRangeToCopy.Copy rStartLocation

is a syntax error.

How can I copy the range to a new location in such a way that the new range (in this case range A2 to D6 of Sheet 2) is stored as a variable?

Upvotes: 2

Views: 210

Answers (2)

Error 1004
Error 1004

Reputation: 8220

You could try:

Option Explicit

Sub test()

    Dim rCopiedRange As Range, rRangeToCopy As Range, rStartLocation As Range
    Dim Rows As Long, Columns As Long

    Set rRangeToCopy = ActiveWorkbook.Sheets(1).Range("C4:F10")
    Set rStartLocation = ActiveWorkbook.Sheets(2).Range("A2")

    rRangeToCopy.Copy rStartLocation

    Rows = rRangeToCopy.Rows.Count
    Columns = rRangeToCopy.Columns.Count

    Set rCopiedRange = ActiveWorkbook.Sheets(2).Range(Cells(rStartLocation.Row, rStartLocation.Column), Cells(Rows + 1, Columns))

End Sub

Upvotes: 2

SJR
SJR

Reputation: 23081

You can use the Resize method as you know the size of destination range will be the same as the source range.

Set rCopiedRange = rStartLocation.resize(rRangeToCopy.rows.count,rRangeToCopy.columns.count)

Using Resize, you could transfer the range directly without involving the clipboard.

Upvotes: 5

Related Questions