Garry,

The macro below runs without error:

Dim rngSource As Range, rngTarget As Range
Set rngSource = Workbooks("Book1.xlsb").Sheets("Sheet1").Range("Na me1")
Set rngTarget = Workbooks("Book2.xlsb").Sheets("Sheet1").Range("A1 ")
rngTarget.Resize(rngSource.Rows.Count, 1).Value = rngSource

However, after the macro runs, the contents of A1:A3 in Book2.xlsb is
empty.


kittronald

Garry,

The process works when I use the code below:

Windows("Book1.xlsb").Activate
Application.Goto Reference:="Name1"
Selection.Copy
Windows("Book2.xlsb").Activate
Range("A1").Select
ActiveSheet.Paste

I was wondering if there was a shorter, more elegant way to get the same
result.


kittronald

modify to suit


Sub copynamedrange()
Range("rng1").Copy Sheets("sheet4").Range("h21")
End Sub



On Sunday, March 11, 2012 7:04:39 AM UTC-5, kittronald wrote:
Garry,

The process works when I use the code below:

Windows("Book1.xlsb").Activate
Application.Goto Reference:="Name1"
Selection.Copy
Windows("Book2.xlsb").Activate
Range("A1").Select
ActiveSheet.Paste

I was wondering if there was a shorter, more elegant way to get the same
result.


kittronald

Don,

Thanks for the reply.

However, I'm not getting it to work between workbooks.


kittronald

For now, the code is working as follows:

Workbooks("Book1.xlsb").Sheets("Sheet1").Range("Na me1").Copy
Workbooks("Book2.xlsb").Sheets("Sheet1").Range("A1 ").PasteSpecial
Workbooks("Book2.xlsb").Names.Add Name:="Name1",
RefersTo:=Sheets("Sheet1").Range("A1:A3")

Thanks for all the help.


kittronald