How to call a macro from another workbook?

Executing macro from same workbook is a routine task in Excel automation. Many a times, We face a situation where we need to a call macro from another workbook. Sometimes, it's very useful to call a macro from another workbook which will help us optimize macro code. Also, it is very easy to modify the code in individual macro of separate workbook.

Taking consideration of these advantage of macro with separate workbook will be used to automate tasks which scheduled on specific time intervals.


Here is the code which will execute macro from another workbook:

Sub Run_Macro_From_Another_Workbook()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' http://vbatricksntips.blogspot.com
    
Dim anotherWbk As Workbook

Application.ScreenUpdating = False

On Error GoTo Err:

' Below lines of code will open workbook where macro is written
Set anotherWbk = Nothing
Set anotherWbk = Workbooks.Open("C:\Users\Dreams\Desktop\Test_Workbook.xlsm")

' Below line of code will execute macro from another workbook
' For example "Test_Macro" is the name of macro from Test_Workbook.xlsm

Application.Run "'" & anotherWbk.Name & "'!Test_Macro"

Err:

If Err.Number <> 0 Then
    MsgBox Err.Description
End If

Application.ScreenUpdating = True

End Sub