How to Call or Run a Another Macro From a Macro

Did you know you can run other macros from a macro?  This is a very efficient practice that allows us to reuse macros, so we don't have duplicate code snippets throughout our macros.

This helps us keeps our macros shorter and easier to manage.  We can have several macros calling another macro.

Run A Macro from Another Macro with the Call Statement in VBA Excel

VBA Example: Run Another Macro from a Macro

Here is an example of how to run another macro from a macro using the Call Statement.

Just type the word Call then space, then type the name of the macro to be called (run).  The example below shows how to call Macro2 from Macro1.

It's important to note that the two macros DO NOT run at the same time.  Once the Call line is hit, Macro2 will be run completely to the end.  Once it is complete, Macro1 will continue to run the next line of code under the Call statement.

Sub Macro1()

    'Place code here to run before calling Macro2
    
    'The following line will run Macro2
    Call Macro2
    
    'When Macro2 is complete the code will continue
    'to run below the call line in this macro

    'Place code here to run AFTER Macro2 runs
    
End Sub

-------------------------------------------------------

Sub Macro2()

    'Place code for Macro2 here

End Sub

Another thing to note is that you do not always have to use the Call statement to call another macro.  You can leave the word Call out, and just type in the macro name.

However, you will need the Call statement if your macro contains parameters (variables) that you want to pass through to the called macro or function.

My personal opinion is that it is best to always use the Call statement when calling another macro.  It may be a few extra letters to type, but it will really help you and others read your code more quickly when debugging it in the future.

Example: Call the Refresh All Pivot Tables Macro from Other Macros

I use this technique of calling other procedures a lot. One common task is to refresh all the pivot tables in the workbook. In the code sample below there are two macros that are both calling the macro to refresh all the pivot tables in the workbook.

Sub Macro1()

    'Code to update the source data
    
    'Run the refresh pivots macro from this macro
    Call Refresh_All_Pivots
    
    'Additional code here

End Sub
-------------------------------------------------------
Sub Macro2()

    'Code to update formulas
    
    'Run the refresh pivots macro from this macro
    Call Refresh_All_Pivots
    
    'Additional code here
    
End Sub
-------------------------------------------------------
Sub Refresh_All_Pivots()
'Refresh all pivot tables caches in the active workbook
Dim i As Integer

    For i = 1 To ActiveWorkbook.PivotCaches.Count
        ActiveWorkbook.PivotCaches(i).Refresh
    Next i

End Sub
  • I have 2 calls in one Macro.

    It will not call the second one,

    If I move the second one and call it first it works fine.

    Any idea why it’s not working?

      • Hey There,
        In need of some help–I am having similar issues as noted above with calling 2 macros

        1st Macro:
        Sub REFRESH()

        ‘ REFRESH Macro
        ‘ Refresh All Queries

        ‘ThisWorkBook.Activate
        Sheets(“Pik Scan”).Activate
        Sheets(“Pik Scan”).Select
        Columns(“A:E”).Select
        Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
        Sheets(“Pik”).Activate
        Sheets(“Pik”).Select
        Columns(“A:G”).Select
        ActiveWorkbook.RefreshAll
        Sheets(“Cust RDRs “).Activate
        Sheets(“Cust RDRs “).Select
        Columns(“A:J”).Select
        ActiveWorkbook.RefreshAll
        End Sub

        Second Macro:(Removing Duplicated Values from Another Sheet that pulls data in from a query)

        Sub RemoveDup()

        ‘ RemoveDup Macro
        ‘ Remove Duplciates from Query


        Range(“A4”).Select
        ThisWorkbook.Sheets(“Sheet2”).Range(“Table_Query_from_sst225[#All]”).RemoveDuplicates Columns:= _
        Array(1, 2, 3, 4), Header:=xlYes
        End Sub

        Command Button:

        Sub Click()

        Call REFRESH
        Call RemoveDup

        End Sub

        Both work independently, when clicking the button data refreshes but on the other sheet the duplicated items are not removed.

        Please any help or assistance is very much appreciated. Thank you.

  • dear sir i am in great trouble please help
    i have 2 different macros i want to merge them
    what i actually want is to run 2nd macro instead of call
    1st:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(“A1:B100”)) Is Nothing Then
    Call Mymacro
    End If
    End Sub
    2nd:
    Sub HURows()
    BeginRow = 9
    EndRow = 30
    ChkCol = 10

    For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = 0 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else
    Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
    Next RowCnt
    End Sub

  • Hi Jon,
    This is asking a lot but I appreciate any help you are able to give.
    I have found this wonderful macro which works very well within a data file. But I want to have another file (call it master file) that I can independently call the data file and extract the information closing it (data file) when done. The master file will retain the data within the summary file it has created.
    I have found macros on the internet that will open the data file but I don’t know how to call the summary macro below:

    Sub Summary_All_Worksheets_With_Formulas()
    Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook

    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With

    ‘Delete the sheet “Summary-Sheet” if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets(“Summary-Sheet”).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    ‘Add a worksheet with the name “Summary-Sheet”
    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets.Add
    Newsh.Name = “Summary-Sheet”

    ‘The links to the first sheet will start in row 2
    RwNum = 1

    For Each Sh In Basebook.Worksheets
    If Sh.Name Newsh.Name And Sh.Visible Then
    ColNum = 1
    RwNum = RwNum + 1
    ‘Copy the sheet name in the A column
    Newsh.Cells(RwNum, 1).Value = Sh.Name

    For Each myCell In Sh.Range(“A1,D5:E5,Z10”) ‘<–Change the range
    ColNum = ColNum + 1
    Newsh.Cells(RwNum, ColNum).Formula = _
    "='" & Sh.Name & "'!" & myCell.Address(False, False)
    Next myCell

    End If
    Next Sh

    Newsh.UsedRange.Columns.AutoFit

    With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End Sub

  • What is the error in the below code. when running it getting error 404
    pls help me out
    Sub Macro1()

    If cell.Value = “U” And cell.Value “” Then
    MsgBox “Enter the reason for unplanned leave”
    End If

    End Sub

  • I am trying to run a singular macro that runs all my other macros. I am an amateur. I have the following:

    Sub RunAll()
    Call Sheet1.CopyrangeA

    Call Sheet13.CopyrangeB
    Call Sheet13.Export_Data2
    Call Sheet14.CopyrangeC
    Call Sheet14.Export_Data3
    Call Sheet15.CopyrangeD
    Call Sheet1.Export_Data1
    Call Sheet15.Export_Data4
    Call Sheet2.ImportIDDE
    Call Sheet7.Import2
    Call Sheet8.Import3
    Call Sheet9.Import4
    Call Sheet11.Import5
    Call Sheet12.Import6
    End Sub

    It runs the first

  • HI Jon,
    could you please help me in providing the VBA code to copy and paste the data which is in protected sheet and send to out look as new email.

    for ex :I have data in sheet2 in col A from row 1 to 15. Sheet1 has formulized data and range A2:I15. Sheet1 has inputs from Sheet2, I have done vlookup for it.
    When I copy Sheet2 A3 and paste it in Sheet1 F5 the data will be updated for that particular code. and the table will be updated and the table range to be copied in to outlook as new mail.
    then Sheet2 A4 to be copied in Sheet1 F5 again and the table will be updated for that particular code. Then the table to be copied and paste into outlook as new email.

    like so on till the Sheet2 A col will reach empty row.. this process has to be done.

    could you please help me to provide the code.

    -Krishna

  • Hi John,

    Written a macro for solve button, it’s working good but after changing inputs it is solving problem directly without touching solver button.
    How to stop solving problem directly?

    Thanks,
    Raj

  • Would it be possible to run a selected Macro from an InputBox variable?
    ex.

    Sub CallCertainMacro()
    Dim name As string

    name = InputBox(“Enter the name of the macro you want to run”)

    Call Name
    End Sub

    Obviously the coding above doesn’t work or I wouldn’t be asking, but do you know of a way to accomplish want I’m attempting here?

  • HI,

    I have a macro which uses variable “x” to run a loop. At the last step within the loop I “call” another macro which also has a variable “x”. My idea was behind this was, once the macro is called it should capture the value of “x” from the main macro and run code from the called macro. It appears this doesn’t work since x is being set to 0. Can you suggest a way around this?

  • I wouldn’t call this example as calling a macro within another macro, I would call this calling a subroutine from another subroutine, with the first subroutine presumably being activated by making it macro, assigning a shortcut key(s) to it, and pressing the shortcut key(s) for that first subroutine. The fact that either or both subroutines have been defined as macros and each assigned a shortcut key(s) is irrelevant.

    What I would call calling a macro from within another macro is to activate the first macro by pressing its shortcut key(s). Then, while this first macro is running, press the shortcut key(s) for the second macro and activate it. I often would like to do this but it doesn’t work because, and it’s been “explained” that the reason that it doesn’t work is because Excel is not multithreaded.

    But that’s a bogus answer and not what I need. What I need is for the second macro to interrupt the first macro, run to completion, and have the first macro resume execution from the point of suspension. It’s not necessary for the first macro to be technically interrupted when the second macro’s shortcut key(s) are pressed. Since VBA is an interpreted language it’s fine if a “macro run pending” flag is set when the second macro’s shortcut key(s) are pressed and only begin to run the second macro when the statement being executing in the first macro terminates execution. This removes the requirement that Excel be multithreaded, it would be exactly like calling a subroutine; the first subroutine (macro 1) gets suspended when (or shortly after) the second subroutine (macro 2) gets activated and resumes execution when the second subroutine (macro 2) terminates.

    This approach has been used in interpreted languages for decades and I have personally made use of it when using interpreters dating back to the 1970s. It’s amazing that the implementers of VBA did not provide for such a simple and useful capability, particularly since they effectively provided the same capability when implementing modeless user forms.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >