Run a Macro from Another Workbook

Question

I have a macro in workbook A that calls a macro in workbook B. I want the macro in workbook B to run and then I want to close workbook B. I keep getting an error saying the macro cannot be found that I want to run from workbook B. I am pretty much a novice at this, but I have done a pretty thorough search and haven't been able to come up with anything on my own. Here is my code in it's entirety.

Public Sub InputDept()


Dim Cap As Workbook
Dim Cap2 As String

On Error Resume Next
Set Cap = Workbooks("NGD Source File for Net Budget Reporting.xlsx")
Cap2 = Cap.Name
On Error GoTo 0

Dim wb As Workbook
Dim Cap1 As Variant

Application.ScreenUpdating = False
If Cap Is Nothing Then
Cap1 = Application.GetOpenFilename("Excel Files(*.xl*)," & "*.xl*", 1)
    If Cap1 = False Then
    Exit Sub
    End If
Set wb = Workbooks.Open(Cap1)
Cap2 = ActiveWorkbook.Name
Else
Workbooks(Cap2).Activate
End If


Sheets("Dept Summary").Activate


Cells.Find(What:="Direct", after:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(1, 0).Select

Range(Selection, Selection.End(xlDown)).Select

Dim cRng As Range
Dim dRng As Range

Set dRng = Selection

For Each cRng In dRng
If cRng.Interior.ThemeColor = xlThemeColorAccent3 Then


    Dim mCalc As String
    Dim mSum As Workbook

    On Error Resume Next
    Set mSum = Workbooks("Master Calc with Macro.xlsm")
    mCalc = mSum.Name
    On Error GoTo 0

    Application.ScreenUpdating = False
    If mSum Is Nothing Then
        mSum1 = Application.GetOpenFilename("Excel Files.xl*),"& "*.xl*", 1)
    If mSum1 = False Then
        Exit Sub
    End If
        Set wb1 = Workbooks.Open(mSum1)
        mCalc = ActiveWorkbook.Name
    Else
        Workbooks(mCalc).Activate
    End If

    cRng.Copy

    Workbooks(mCalc).Activate
    Sheets("Data").Select
    Range("A5").Select

    Selection.PasteSpecial Paste:=xlPasteValues
    Sheets("Report").Activate

    Workbooks(mCalc).Application.Run ("!SummarizeMaster")

    Sheets("Report").Select
    ActiveSheet.Copy
    Cells.Select
    Cells.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.SaveAs _
        Filename:=Application.ThisWorkbook.Path & "\" & Format(Date -        28, "MMM") & " Files\" & Left(cRng, 7) & ".xlsx"

    ActiveWorkbook.Close

    Workbooks(mCalc).Close savechanges:=False

End If
Next cRng



End Sub

From [this](http://stackoverflow.com/questions/24434011/excel-vba-run-macro-open-another-file-run-that-files-macro-save-close), looks like you don't need the `()`? Btw, you can use `wb1` instead of `Workbooks(mCalc)`. — findwindow, Dec 10 '15 at 20:59

score 26 · Accepted Answer · edited Feb 08 '19 at 15:27

26

This line:

Workbooks(mCalc).Application.Run ("!SummarizeMaster")

needs to be changed a little. You need to include the name of the workbook inside a single quotes, even if it looks like you are specifying the proper workbook with Workbooks(mCalc):

Workbooks(mCalc).Application.Run ("'Master Calc with Macro.xlsm'!SummarizeMaster")

You can actually just shorten it to:

Application.Run ("'Master Calc with Macro.xlsm'!SummarizeMaster")

edited Feb 08 '19 at 15:27

Mahhdy

592
9
25

answered Dec 10 '15 at 21:07

Stewbob

16,759
9
63
107

Thanks for the answers. @Stewbob - tried this fix and now I am getting an error that says "Run-time erro '1004': Cannot run the macro 'Master Calc with Macro.xlsm!SummarizeMaster'. The macro may not be available in this workbook or all macros may be disabled. – JudeD Jan 05 '16 at 15:12
1

Normally, the `1004` error indicates that an object doesn't exist or can't be found. Maybe check the spelling of everything? – Stewbob Jan 05 '16 at 15:19
So, I figured out that you need a space between the file name and the marco, ie Application.Run ("Master Calc with Macro.xlsm !SummarizeMaster"). However, now I am getting an error that says Excel can't open two workbooks with the same name at the same time??? – JudeD Jan 06 '16 at 18:40
3

I googled again and found the answer on another post I didn't see before. Apparently, there needs to be single quotes around the file name only, so eg. Application.Run ("'Master Calc with Macro.xlsm'!SummarizeMaster"). This works! – JudeD Jan 06 '16 at 18:51

score 5 · Answer 2 · answered May 09 '17 at 07:03

If the macro you need to find relative macro path by using workbook path from which you run macro and you need to run several macros from the array list, the code below will help:

Dim relativePath As String, programFileName As String
Dim selectedProgramsFiles() As String, programsArrayLastIndex As Byte, I As Byte

For I = 0 To programsArrayLastIndex 'Loop through all selected programs
    programFileName = selectedProgramsFiles(I)
    relativePath = ThisWorkbook.Path & "\" & programFileName
    Workbooks.Open Filename:=relativePath

    Application.Run ("'" & relativePath & "'!ModuleName.Main")

   Workbooks(programFileName).Activate
   ActiveWorkbook.Close SaveChanges:=False
Next I 'For I = 0 To programsArrayLastIndex 'Loop through all selected program

Workbooks.Open .... line is unneccessary, because file any way will be open in line Application.Run — Sharunas Bielskis, Jun 03 '17 at 06:25

score 1 · Answer 3 · edited Jun 14 '19 at 20:39

Application.Run "PERSONAL.xlsb!ClearYellow", 0

ClearYellow is the name of the sub in Personal.xlsb that is being run. The "0" is the first argument of this sub (would omit if no arguments, could add more arguments separated by commas)

Application does not seem to be needed

This could be used to run from some other workbook also; the workbook would have to be open; if the name of that workbook had a space in it, the name would have to be surrounded by ''

Call does not work cross workbooks; haven’t tested within same workbook or within same module

Run a Macro from Another Workbook

3 Answers3

Linked

Related