- Have 2 workbooks : 1. Jan. Test.xlsm which is the (destination) workbook and
12. Dec.Test.xlsm which is the source book
2. Both work books have identical sheets and named ranges
I am trying to create a link via vba
FROM
12. Dec.Test.xlsm , Sheets“Ιncome_and_Expenses”.Range(“B15”) which as a defined name with Scope This workbook of “EndDecWeOwe” and contains the numerical value 100
TO
1. Jan.Test.xlsm , Sheets“Ιncome_and_Expenses”.Range(“B2”) which as a defined name with Scope This workbook of “Prior_EndDecWeOwe”
The Full path of the source book is a result of a formula which resides in destination book in
Sheets(“Formulas etc”).Range (“D54”) as follows:
C:\Users\ele\Desktop\&"["&IF(B8=2;VLOOKUP(13-1;A10:C22;1);VLOOKUP(B8-2;A10:C22;1))&". "&IF(B8=2;VLOOKUP(13;A10:C22;3);VLOOKUP(B8-1;A10:C22;3))&" "&IF(B8=2;B4-1;B4)&" "&"Test.xlsm]Income_and_Expenses!EndDecWeOwe"
By putting the = sign in front of it we have
="C:\Users\ele\Desktop"&"["&IF(B8=2;VLOOKUP(13-1;A10:C22;1);VLOOKUP(B8-2;A10:C22;1))&". "&IF(B8=2;VLOOKUP(13;A10:C22;3);VLOOKUP(B8-1;A10:C22;3))&" "&IF(B8=2;B4-1;B4)&" "&"Test.xlsm]Income_and_Expenses!EndDecWeOwe"
And resolves in Sheets(“Formulas etc”).Range (“D56”) to:
C:\Users\ele\Desktop\[12. Dec. 2016 Test.xlsm]Income_and_Expenses!EndDecWeOwe
I have even tried to define Name for this as: FormulaEndDecWeOwe to simplify matters.
In
1. Jan.Test.xlsm , Sheets“Ιncome_and_Expenses”.Range(“B2”)/(“Prior_EndDecWeOwe”)
I have tried this below and other combination we the same result
Sub6 ()
Sheets("Income_and_Expenses").Select
Range("Prior_EndDecWeOwe").Select
ActiveCell.Value = " ='Sheets('Formulas etc').Range('D56').value"
End Sub
This results in Range("Prior_EndDecWeOwe")
='Sheets('Formulas etc').Range('D56').value as TEXT instead of the correct:
ActiveCell.FormulaR1C1 = "='12. Dec. 2016 Test.xlsm'!EndDecWeOwe"
I have tried other formulations but all result in text
'ActiveCell.FormulaR1C1 = Range("FormulaEndDecWeOwe").Value