CODEWITHSUNDEEP
vbaexcel
Dane O'Connor
Dane O'Connor

Reputation: 77368

How do I copy a range in vba?

I'm trying this but its giving me a type-mismatch. Is there another way to do this? I'm assuming rSource is not coming from the active sheet.

Also, I only need values.

Edit: The rSource is expected to have rows and columns. It will also be a single continuous area.

Edit: Once this operation is complete I should be able to filter each range independently.

Function CopyRange(rSource As range) As range
    ' Declarations
    Dim rTemp As range

    ' Create new range starting at cell A1
    Set rTemp = Range(Cells(1, 1), Cells(rSource.Rows.Count, rSource.Columns.Count))

    rTemp.Value = rSource.Value

    Set CopyRange = rTemp
End Function

Upvotes: 1

Views: 10550

Answers (3)

Adam Ralph
Adam Ralph

Reputation: 29956

Assuming this code is in the code module for a Worksheet then it should work just fine. I just tried it and it runs fine for me (assuming the range passed to the function is contiguous).

Alternatively, try this:-

Function CopyRange(rSource As Range) As Range

   Call rSource.Copy(Cells(1, 1))

   Set CopyRange = Range(rSource.Address).Offset(1 - rSource.Row, 1 - rSource.Column)

End Function

Upvotes: 1

Bravax
Bravax

Reputation: 10493

I believe your function is fine.

I created a teststub:

Function Check()
  Dim x As Range
  Dim c As Range
  Set c = Range("A1", "A2")
  Set x = CopyRange(c)
End Function

With the cells A1 set to 2 and A2 set to 3.

On stepping into this, and evaluating the x variable using:
?x.Range("A1")
(I got Type mismatch with ?x as you did.)

I get 2.

Hope that helps.

Upvotes: 1

CABecker
CABecker

Reputation: 161

Is this a continuous area? Is it bounded by empty cells? If so you should be able to use the currentregion property to set the range.

I am thinking the reason you are getting an error is because you are not setting rSource in your first cells declaration.

Upvotes: 0

Related Questions