VBA Question - Activate a Macro from another workbook or activate a hyperlink from another workbook - Excel 2010

[Johnny Thunder]

Hello all,


What I am working on - I have a Master file that will open another workbook based on a list and the newly opened file will have a hyperlink on Cell ("C14") when you manually click the cell it Calls Macro1*. The problem I am having is in my research for this process I found this below

*Macro1 is a Addrows Macro

Application.Run ("'Analytics macro S17.xlsm'!getdata")

This won't work for me because I use a list Dimmed as a String to bring the File name and I wasn't able to make this work.

Here is my code so you can maybe see something I am not seeing.

  'Defines file name
   varCellvalue = Sheets("Main").Range("B23").Value

  'Defines Type of agreement and assigns Sheet to find
   varcell = Sheets("Main").Range("B19").Value
   

   'Defines newly opened Workbook
   Dim destbook As Workbook
 
       Workbooks.Open ("\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xlsm")
       Set destbook = ActiveWorkbook

   'Here is where I would enter something like 
     Application.Run (destbook & "!Macro1")

The other alternative is to activate a hyperlink from the newly opened workbook but I think calling the Macro1 is the more direct approach. Any help is always appreciated!

Thanks in advance for all who help.

</pre>

 

[SMB Cooper]

The other alternative is to activate a hyperlink from the newly opened workbook but I think calling the Macro1 is the more direct approach. Any help is always appreciated!

I think you should record a macro from the master file... to see the default codes that it will generate.

 

[Johnny Thunder]

Not sure why I didn't think if that but it got the macro to run but in the macro I have a pop-up box that is "Dim as Integer" for a user to populate it with how many new rows you want added, when I was recording the macro and entered in a quantity the recorder didn't capture that function, any ideas on how to get that into the macro?