Copying a Name's Contents

[kittronald]

In a macro, how would you copy a defined name's contents from one
workbook to another ?

Book1.xlsb has a defined name "Name1" with a Refers To field of
"=Sheet1!$A$1:$A$3"

A1 = Orange

A2 = Apple

A3 = Pear

The contents of A1:A3 need to be copied to Sheet1!A1:A3 in Book2.xlsb.


kittronald

 

[GS]

It happens that kittronald formulated :

In a macro, how would you copy a defined name's contents from one
workbook to another ?

Book1.xlsb has a defined name "Name1" with a Refers To field of
"=Sheet1!$A$1:$A$3"

A1 = Orange

A2 = Apple

A3 = Pear

The contents of A1:A3 need to be copied to Sheet1!A1:A3 in Book2.xlsb.


kittronald

Try...

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[kittronald]

Garry,

When I run the macro, an error occurs:

Run-time error '424':

Object required



kittronald

 

[GS]

GS was thinking very hard :

It happens that kittronald formulated :

Try...

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

You need to replace Book1/Book2 with the actual workbook refs...

Workbooks("Book1Name").Sheets("Sheet1").Range("Name1Name")

...and so forth.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[kittronald]

Garry,

The macro below runs without error:

Dim rngSource As Range, rngTarget As Range
Set rngSource = Workbooks("Book1.xlsb").Sheets("Sheet1").Range("Name1")
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

 

[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

 

[Don Guillett]

modify to suit


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

 

[kittronald]

Don,

Thanks for the reply.

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


kittronald

 

[kittronald]

For now, the code is working as follows:

Workbooks("Book1.xlsb").Sheets("Sheet1").Range("Name1").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