Setting name value across workbooks

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

kittronald

In a macro, how can you take the evaluated value of the defined name,
"Name1", in Book1.xlsb and write it to the .Refersto field of the defined
name, "Name2", in Book2.xlsb ?

In Book1.xlsb, the defined name "Name1" has a Refersto field of
=SUM(1,1)

In Book2.xlsb, the defined name "Name2" should have a value of "2"
written to it.



kittronald
 
kittronald said:
In a macro, how can you take the evaluated value of the defined name,
"Name1", in Book1.xlsb and write it to the .Refersto field of the defined
name, "Name2", in Book2.xlsb ?

In Book1.xlsb, the defined name "Name1" has a Refersto field of
=SUM(1,1)

In Book2.xlsb, the defined name "Name2" should have a value of "2"
written to it.
Click to expand...

Evaluate:
MsgBox Evaluate("=SUM(1,1)")
Name2.Value = Evaluate(Name1.Refersto)
 
After entering the following:

Sub Test()
Workbooks("C:\Temp\Book2.xlsb").Range("Name2").Value =
Application.Evaluate(Workbooks("C:\Temp\Book1.xlsb").Range("Name1").RefersTo)
End Sub

Why is the following error occuring ?

Run-time error '9':

Subscript out of range


kittronald
 
I've got this working as follows:

Workbooks.Open Filename:="C:\Temp\Book2.xlsb"
Workbooks("Book2.xlsb").Names("Name2").RefersTo =
Application.Evaluate(Workbooks("Book1.xlsb").Names("Name1").RefersTo)
Workbooks("Book2.xlsb").Save
Workbooks("Book2.xlsb").Close

Is there any way to reduce this code ?



kittronald
 
Workbooks.Open Filename:="C:\Temp\Book2.xlsb"
Workbooks("Book2.xlsb").Names("Name2").RefersTo =
Application.Evaluate(Workbooks("Book1.xlsb").Names("Name1").RefersTo)
Workbooks("Book2.xlsb").Save
Workbooks("Book2.xlsb").Close
Click to expand...

Try:
Workbooks.Open Filename:="C:\Temp\Book2.xlsb"
Workbooks("Book2.xlsb").Names("Name2").RefersTo =
Application.Evaluate(Workbooks("Book1.xlsb").Names("Name1").RefersTo)
Workbooks("Book2.xlsb").Close NoSave
HTH
Mick.
 
Mick,

I do need to save the file being closed.

Does the code below save only Book2.xlsb upon closing or does it save
all open files ?

Workbooks("Book2.xlsb").Close Save


kittronald
 
Back
Top