CODEWITHSUNDEEP
vba
Aaron
Aaron

Reputation: 23

How to call a macro from another workbook?

I deal with order processing for a few retailers at my business and I manipulate the orders in excel worksheets. I import data from a query for each retailer and this process is the same across the 4 or 5 different accounts. But later steps differ, so each retailer has its own workbook.

What I am attempting to do is automate the process of importing the data from the query to each workbook through the use of formulas in the workbook as well as VBA. But I wanted to consolidate the macros into one workbook (basically a macro database other worksheets can call from) since I don't want to have to change the code in 5 separate workbooks every time I make an adjustment.

I have already attempted to put the code in the macro database and then just call the macro from each individual workbook and some of it has somewhat worked.

Here is one of the macros I originally had in each workbook and it works fine when in each workbook.

Private Sub OrderNumbers()

Dim myValue1 As Variant
Dim myValue2 As Variant
myValue1 = InputBox("What is your first order number?")
myValue2 = InputBox("What is your last order number?")
Range("A7").Value = myValue1
Range("A9").Value = myValue2

End Sub

Here is another macro.

Private Sub FillFormulas()

Dim myValue3 As Long
myValue3 = Range("A13").Value
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & myValue3)
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & myValue3)
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & myValue3)
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E" & myValue3)
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & myValue3)
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & myValue3)
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & myValue3)
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & myValue3)
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J" & myValue3)

End Sub

I then moved the code to the database, and in each retailer workbook I put this code connected to buttons:

Private Sub Start_Click()
Excel.Run "Macros.xlsm!OrderNumbers()"
End Sub

For the OrderNumbers() macro, it calls it, but it calls it twice for some reason. However, the FillFormulas() macro doesn't work at all.

Upvotes: 1

Views: 625

Answers (1)

TourEiffel
TourEiffel

Reputation: 4414

What about Application.Run

Application.Run "'Another Workbook.xlsm'!NameOfMacro"

The ' marks are needed when the workbook name contains a space. If there is no space, the ' marks are not required. The code will still run correctly if they are included, so I recommend to use them for consistency.

NOTE : That the workbook which contains the macro MUST be open. If the workbook isn't open you will need to open it before runing the macro.

Hope this helps, Waiting for your feedbacks.

BONUS It's better to avoid select so I did it for you : 

Private Sub FillFormulas()

Dim myValue3 As Long
myValue3 = Range("A13").Value
Range("B2").AutoFill Destination:=Range("B2:B" & myValue3)
Range("C2").AutoFill Destination:=Range("C2:C" & myValue3)
Range("D2").AutoFill Destination:=Range("D2:D" & myValue3)
Range("E2").AutoFill Destination:=Range("E2:E" & myValue3)
Range("F2").AutoFill Destination:=Range("F2:F" & myValue3)
Range("G2").AutoFill Destination:=Range("G2:G" & myValue3)
Range("H2").AutoFill Destination:=Range("H2:H" & myValue3)
Range("I2").AutoFill Destination:=Range("I2:I" & myValue3)
Range("J2").AutoFill Destination:=Range("J2:J" & myValue3)

End Sub

Upvotes: 4

Related Questions