Setting name value across workbooks

[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

 

[Auric__]

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.

Evaluate:
MsgBox Evaluate("=SUM(1,1)")
Name2.Value = Evaluate(Name1.Refersto)

 

[kittronald]

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

 

[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

 

[Vacuum Sealed]

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

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.

 

[kittronald]

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