How to copy range links to addin worksheet and then create a named range from it?

Question

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?

FaneDuru FaneDuru · Accepted Answer · 2020-05-30T19:30:13

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

How to copy range links to addin worksheet and then create a named range from it?

1 Answers