VBA to Copy and Paste

[Magdoulin]

Hi guys, how is everything?
I seek your help to assist me with the following

I have a workbook called A, for example, it contains a dynamic hyperlink that I’ll control by some way

Anyways, supposedly, that this hyperlink now refers to a named range in another workbook, this workbook is called B

I need to write a macro code in workbook A that will copy the named range in the other workbook B that this hyperlink currently refers to, and pasted it as a picture in a specific cell (say, AA2) in my first workbook A.

How to do so?

 

[Yongle]

Try this

The VBA
- follows the hyperlink and opens FileB
- gets the sheet and range from the named range RefersTo
- copies that range
- reactivates FileA
- selects required cell
- pastes as a picture
- closes FileB

Amend red values to those that will work in your workbooks

Sub FollowLinkCopyAndPaste()
[I][COLOR=#000080]Run from sheet containing the hyperlink[/COLOR][/I]
    Const CellWithLink = "[COLOR=#ff0000]A2[/COLOR]"
    Const NamedRange = "[COLOR=#ff0000]NameOfRange[/COLOR]"
    Const PastePictureHere = "[COLOR=#ff0000]AA2[/COLOR]"
    
    Application.ScreenUpdating = False
    Dim wb As Workbook, refer As String, sh As Worksheet, rng As Range
    Range(CellWithLink).Hyperlinks(1).Follow
    Set wb = ActiveWorkbook
    refer = wb.Names(NamedRange).RefersTo
    Set sh = wb.Sheets(Replace(Replace(Split(refer, "!")(0), "'", ""), "=", ""))
    Set rng = sh.Range(Split(refer, "!")(1))
    rng.Copy
    Windows(ThisWorkbook.Name).Activate
    Range(PastePictureHere).Select
    ActiveSheet.Pictures.Paste
    wb.Close False
    Application.ScreenUpdating = True
End Sub

Notes:

FileB must contain required named range
Cell A2 in FileA must contain working hyperlink to FileB