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.
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?
Hi Stacy,
It would be hard for me to figure it out without seeing the code. You can post it here.
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
what are the requirements for where the Macro your calling is stored? in the same VBA project?
Why is it that when I change the value in the cell, the macro is called, but does not run.
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.
Hi Jon,
Firstly, I would like to say that I am deeply impressed by your work and your extremely in depth explanation of every tutorial of yours!
I have written a code on PERSONAL.XLSB and inside that code, I am creating a new sheet on the active workbook and am doing lots of other work.
Then I am wanting to add a button on that newly created sheet, clicking on which the user can directly draw a connector shape (rather than go to Insert Ribbon->Illustrations->Shapes->Desired connectors shape).
This is because there will be many such shapes that the user is supposed to draw, so I want to create a nice small button, clicking on which the user will directly get the drawing mode on the mouse and the user can directly start drawing.
I have made some progress with this, but for some reason, although the drawing module (which contains the code enabling the drawing mode) is being run when the button is cliked, but still I am not getting that drawing mode. However if I run that particular module from VBEditor, then it is working absolutely fine.
Can you please help me out with this? I am attaching relevant portions of both the modules.
Actual Module:-
Dim btn As Object
Set btn = ActiveSheet.Buttons.Add(110, 0, 100, 15)
With btn
.OnAction = “LineDrawButton_Click”
.Caption = “Draw connection”
.name = “Draw_connection”
End With
Drawing Module:-
Public Sub LineDrawButton_Click()
‘MsgBox Application.Caller
Application.CommandBars(“Connectors”).FindControl(ID:=1042).Execute
End Sub
I can see that the Drawing Module is running becuase I am getting the msgbox, but I don’t know why the 2nd line is not running as expected if run through the button. (i.e. drawing mode is not appearing on clicking the button, but is appearing on running the Drawing module from VBEditor).
Any kind of help would be appreciated!
Thanks a lot!