1

Please your help with the following:

  1. I need to call a macro which name is variable and is in cell C5 of Sheet1 of Workbook1.
  2. I need to run that macro in another workbook, let's say Workbook2.

Code that I have (in Workbook1) so far is:

Public Sub RS()
    'Setting source worksheet and workbook where the macros are saved
    Dim ws1 As Worksheet
    Dim wb1 As Workbook
    Set wb1 = ActiveWorkbook
    Set ws1 = ActiveWorkbook.ActiveSheet

    'Defining the cell that contains the name of the chosen macro to be run
    Dim Macro1 As String
    Macro1 = Range("C5").Value

    'Selecting target workbook
    Workbooks("Workbook2").Activate
    ActiveSheet.Select
    Dim ws2 As Worksheet
    Dim wb2 As Workbook
    Set wb2 = ActiveWorkbook
    Set ws2 = ActiveWorkbook.ActiveSheet

    'Running in Workbook2, the macro selected in workbook1
    Call Macro1
End Sub

The problem is that Macro1 has not been recognized. I've been also trying with CallByName function, and Application.Run Macro1, but no luck either. Please tell me what I'm missing or what I should modify.

L42
  • 19,427
  • 11
  • 44
  • 68
Flavinho
  • 19
  • 2
  • 6
  • 1
    I'd highly recommend [Avoiding Using `Select`, `Activate`, and `ActiveBook/Sheet`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Chrismas007 Jan 20 '15 at 20:26

2 Answers2

1

To call a macro from another Workbook, you need to include the name of that workbook in Application.Run Method's first argument. Try:

Application.Run "WBNameThatContainsTheMacro.xlsm!" & Range("C5").Value

If you have multiple modules in that workbook, you can be explicit as well:

Application.Run "WBNameThatContainsTheMacro.xlsm!" & "Module1." & Range("C5").Value

Where Module1 is the name of the module. Take note of the dot that separate the module name and macro name. Is this what you're trying? HTH.

L42
  • 19,427
  • 11
  • 44
  • 68
0

Replace the line 

    Call Macro1

with this:

    Application.Run "'" & wb2.Name & "'!VBAProject." & ws2.CodeName & "." & Macro1

Your code implies the "Macro1" is in the ws2's code module. If it is located in another module of the workbook "wb2" then replace "ws2.CodeName" above with the codename of that module.

2nd Update based on info from Flavinho.The codeline becomes this:

    'Application.Run "Workbook1.xlsm!VBAProject.Module3.ts1"
    Application.Run "Workbook1.xlsm!VBAProject.Module3." & ts1
Gene Skuratovsky
  • 591
  • 2
  • 6
  • Thanks L42, and Gene Skuratovsky for your responses! I went with: Application.Run "Workbook1.xlsm!" & "Module3." & ts1 but I got the following error: "Run-time error '1004': Cannot run the macro... The macro may not be available in this workbook or all macros may be disabled." What would be the problem now? Thanks a lot! – Flavinho Jan 20 '15 at 17:49
  • Please ignore the above :( – Gene Skuratovsky Jan 20 '15 at 18:02
  • Macro is in Workbook1 and needs to be run in Workbook2. Thanks. – Flavinho Jan 20 '15 at 19:55
  • ts1 is defined as follows: Dim ts1 As String ts1 = Workbooks("Workbook1.xlsm").Sheets("Sheet1").Range("C5").Value The text of cell C5 contains the name of the macro to be run – Flavinho Jan 20 '15 at 20:37
  • I've updated the answer again and it is basically what you've tried, except for the "VBAProject" bit. If this does not work (and it absolutely should!) there is something entirely different at paly here... – Gene Skuratovsky Jan 20 '15 at 23:56