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?

  • 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

    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

    >