Call Macro from Hyperlink using multiple workbooks without re-opening Macro Sheet

Question

I have 2 Workbooks:

Workbook With Macros

Workbook to write to.

I have 2 subs. One is to insert a hyperlink in the 'workbook to write to' and the other Sub will execute when the hyperlink is clicked.

However, my sub to create the Hyperlink is causing me some confusion. I need to reference the worksheet in the Macro Enabled Workbook, but doing that requires a Workbook.Open command. Obviously the Macro Enabled Workbook will already be open so this closes and re-opens it. I've gotten very muddled with this, can someone point me in the right direction?

So Macro Sheet will have the below Sub, the link is to another Sub in the same sheet. "CreateHyperlinks" is called from another method which writes to an external spreadsheet.

Obviously "ActiveSheet" below is wrong. I want to write to a different spreadsheet, so I will need to open it also (I assume)?

Or, can I pass the worksheet that is being written to from it's write method which is calling "CreateHyperlinks" or am I coupling everything too much as it is?

' This is called elsewhere
Sub CreateHpyerlinks(rangeValue, textValue)
    Dim fileName As String
    Dim wb As Workbook
    Dim TheSheet As Worksheet
        
    
    fileName = "c:\blah\blah.xlsm"
    
    ' ** This is the part: How do i reference "TheSheet" without opening the XL?
    Set wb = Workbooks.Open(fileName:=fileName)
    Set TheSheet = wb.Sheets("MasterCard")
    TheSheet.UsedRange.Select       
   
   ActiveSheet.Hyperlinks.Add Anchor:=rangeValue, Address:=TheSheet!THISISMYMACROHERE(textValue), SubAddress:="", ScreenTip:="Go to Word Documebnt", TextToDisplay:=textValue
    
End Sub

---

UPDATED:

I have updated the Sub, and am hitting Object does not support his method or property

Sub CreateHpyerlinks(rangeValue, textValue)
    Dim fileName As String
    Dim wb As Workbook
    Dim wbWrite As Workbook
    Dim TheSheetWithMacros As Worksheet
    Dim TheSheetToWriteTo As Worksheet
    
    fileName = "c:\WorkbookToWriteTo.xlsx"
    
    Set wb = Application.Workbooks(ThisWorkbook.Name)
    Set TheSheetWithMacros = wb.Worksheets("Sheet1")

    Set wbWrite = Workbooks.Open(fileName:=fileName)
    Set TheSheetToWriteTo = wbWrite.Worksheets("Sheet1")
    
    ' This Line Errors:
    TheSheetToWriteTo.Hyperlinks.Add Anchor:=rangeValue, Address:="", SubAddress:=TheSheetWithMacros!Goto80BytePopulationGuide(textValue), ScreenTip:="Call Macro", TextToDisplay:=textValue
        
    wbWrite.Save
    wbWrite.Close
    
End Sub

The line TheSheetToWriteTo.Hyperlinks.Add Anchor:=rangeValue, Address:="", SubAddress:=TheSheetWithMacros!Goto80BytePopulationGuide(textValue), ScreenTip:="Call Macro", TextToDisplay:=textValue is at fault, clearly TheSheetWithMacors!CallMacro doesn't work like I had hoped.

Answer 1

You may be able to copy the Module's code from one workbook to the other workbook. I assume for now that the Worksheet module only contains the macro and nothing else (or that it is OK to copy everything in that module).

So the reason I propose this, is that if you can copy it, then you can point the hyperlink to a local (within the same file) macro -- because we're copying the module to the new file -- which I think you have already been able to do.

This requires you to check the trust center setting to allow access to the VBProject. A prompt will ask you to do that before it can proceed.

Sub writeit()
'copies a specified code module from one workbook, to
' a code module in another workbook
'
'ASSUMPTIONS:
'-the specified destination must already exist
'-the specified destination does not already contain
' any procedures that would conflict naming with the
' copied module procedures
MsgBox "Please make sure to enable access to ""Trust access to the VBA Project Object Model""", vbInformation
Application.CommandBars.ExecuteMso("MacroSecurity")


Dim macrobook As Workbook
Dim otherbook As Workbook

Dim lines As String
Dim destModule As Object 'VBComponent
Dim copyModule As Object 'VBComponent


Set macrobook = ThisWorkbook  'Modify as needed
Set copyModule = macrobook.VBProject.VBComponents("Sheet1")  'Name of your module to copy

Set otherbook = Workbooks(2)  'Modify as needed
Set destModule = otherbook.VBProject.VBComponents("Sheet1")      'name of where it will go

'Appends the lines from CopyModule to DestModule
With destModule.CodeModule
     .InsertLines .CountOfLines + 1, copyModule.CodeModule.lines(1, copyModule.CodeModule.CountOfLines)
End With


End Sub