Copying external Excel range into memory

I have an external excel spreadsheet which I want to open to get a range, copy this into memory and close the source workbook.

The code I have to test this looks like this:

Set wkbSource = Workbooks.Open(sWkbSourcePath)
Set rngList = wkbSource.Sheets("Sheet1").Range("rgnList")

Debug.Print "The original:" & rngList.Cells(1, 2) & " is a " & rngList.Cells(1, 1)

rngList.Copy Destination:=rngCopy

wkbSource.Close

Debug.Print "The copy:" & rngCopy.Cells(1, 2) & " is a " & rngCopy.Cells(1, 1)开发者_如何学JAVA
Debug.Print "---END---"

The second debug print does nothing. Instead of

 rngList.Copy Destination:=rngCopy

I also tried this

 rngCopy.value = rngList.value 

and I tried to store the value of the range in variant.

None of this works. Can anyone point me in the right direction?

---

When copying Range object, you should specify another Range object as destination. In other case, range will be copied into Clipboard.

Destination   Optional Variant. Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard.

Worksheets("Sheet1").Range("A1:D4").Copy _
    destination:=Worksheets("Sheet2").Range("E5")

In your code you passing empty variable, so, it fails.

---

Assuming named range rngList is defined in source workbook, and consistent with the definition of rngCopy

Option Explicit
Sub a()
Dim r, rngCopy As Range
Dim wkbSource As Workbook

Set rngCopy = Range("A1:D8")
Set wkbSource = Workbooks.Open("c:\mata.xls")
Set r = wkbSource.Sheets("Sheet1").Range("rngList")

Debug.Print "The original:" & r.Cells(1, 2) & " is a " & r.Cells(1, 1)

r.Copy Destination:=rngCopy
wkbSource.Close

Debug.Print "The copy:" & rngCopy.Cells(1, 2) & " is a " & rngCopy.Cells(1, 1)
Debug.Print "---END---"

End Sub