Copying a Name's Contents
 

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

Copying a Name's Contents
 

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

Copying a Name's Contents
 

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

Copying a Name's Contents
 

Don,

Thanks for the reply.

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


kittronald

Copying a Name's Contents
 

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