Copying a Name's Contents

  • Thread starter Thread starter kittronald
  • Start date Start date
K

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
 
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
Click to expand...

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
 
Garry,

When I run the macro, an error occurs:

Run-time error '424':

Object required



kittronald
 
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
Click to expand...

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
 
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
 
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
 
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("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
 
Back
Top