Is it possible to link selected range (copy with links?) to addin worksheet, then create a named range in that addin workbook and then finally create a named range, which refers to that named range stored in addin?
For example, I have this data:
I am trying to copy it with links to the addin worksheet. Addin is called "addin_test.xlam" and the worksheet stored in it is called "ws_test". I have this piece of code:
Dim rng As Range
Set rng = Selection
rng.Copy
ThisWorkbook.Sheets("ws_test").Range("A1").PasteSpecial
(How to copy links, instead of absolute values? I would like my addin sheet update its data according to activesheet values)
Then I create a named range in my addin workbook:
ThisWorkbook.Names.Add Name:="Test_addin_name", RefersTo:=ThisWorkbook.Sheets("ws_test").Range("A1:A3")
Lastly, I am looking for a way to create a named range in activesheet (open workbook) and link it to the named range in my add-in. This is what I have so far:
ActiveSheet.Names.Add Name:="Test_sheet_name", RefersTo:=ThisWorkbook.Name & "!" & ThisWorkbook.Names("Test_addin_name").Name
Named range is created correctly but sadly it returns "string" value of what I typed after RefersTo parameter. Value of this named range is not {100,200,300} but "WorkbookName...". I have tried different things but nothing seems to be working.
Can somebody help me?
Change, please changing
RefersTo:=ThisWorkbook.Name & "!" & ThisWorkbook.Names("Test_addin_name").Name
with
"[" & ThisWorkbook.Name & "]ws_test!" & ThisWorkbook.Names("Test_addin_name").RefersTo
Related to linking solution, please try the next code. It writes a formula in the range you want being updated when the add-in Name
is changed:
Sub LinkAddinName()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Range("A1").Formula = "=COUNTA('" & ThisWorkbook.Name & "'!Test_addin_name)"
End Sub
Instead of CountA
you can use any formula using the range. If you need to have a similar range, you must write formulas for each cell to be linked to the correspondent cell of the named range:
Sub LinkAddinNameEachCell()
Dim sh As Worksheet, cel As Range
Set sh = ActiveSheet
For Each cel In ThisWorkbook.Names("Test_addin_name").RefersToRange
sh.Range(cel.address).Formula = "='[" & ThisWorkbook.Name & "]ws_test'!" & cel.address
Next
End Sub