How to pass named range in vba

[pilot]

I posted a similar question yesterday and got no takers. With wb1 and wb2 open, wb2 is active, I want vb code to place a named cell from wb1 into A1 of wb2. I found a way that works (below) but I'm sure it's not the cleanest approach.

Windows("wb1.xls").Activate
mycell = ActiveWorkbook.Sheets("SDA").Range("mycell")
Windows("wb2.xls").Activate
ActiveWorkbook.Sheets("Unpaid").Range("A1") = mycell

 

[Richie(UK)]

Hi Pilot,

How about something like this:<pre>
Sub copyit()

Workbooks("wb1.xls").Worksheets("SDA").Range("mycell").Copy _
Destination:=Workbooks("wb2.xls").Worksheets("Unpaid").Range("A1")

End Sub</pre>
HTH

EDIT: Changed sheet names to OP's sheet names.
PS. I seem to remember reading somewhere that it is quicker not to copy&paste. I suggest you use Smozgur's solution (see below).
_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-08-29 05:53

 

[smozgur]

Hi,

No need using Activate method.

Workbooks("wb2.xls").Sheets("Unpaid").Range("A1") =Workbooks("wb1.xls").Sheets("SDA").Range("mycell")

Suat

 

[pilot]

Thanks, both of you. I knew it was elementary, I just couldn't get all the language just right. I really need to get a good book on VBA.