Copy a named range from an external worksheet

[David Evans]

into my current workbook. Can I do that without opening up the source? Can i just point to the source and reference the range and copy it to (the same cell ref) in my current workbook?

Any great insight always appreciated and welcomed

D

 

[David Evans]

Sub CopyRange()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Workbooks("Blank Sheet.xlsm").Sheets("Summary").Range("Blank_Data")
Set Rng2 = Workbooks("All Inclusive Fee Calculation Final Version").Sheets("Summary").Range("A9")

Rng1.Copy Rng2

End Sub

I'm getting so good at answering my own questions - I put it down to my Chandoo attitude ...

However, the above code ONLY works if I have the file Blank Sheet open - I'd prefer that it be closed as it will only cause confusion - any ideas on how to reference it when it's 'closed'?