I need help to merge 2 macros

Question

Hello,

I need help to merge 2 macros. Bascially, after "MACRO1" finishes running in File 1 ( macro 1 itself located in file 1), i want excel to run " MACRO 2" in File 2 (macro 2 itself located in file 2) . I tried using the following code:

Windows("2.xls").Activate
Application.Run "2.xls!Macro2"

However, Instead of running in file 2, it runs in file 1 which overwrite all the original data i had in File 1. ( It goes back to 1.xls even i gave the code Windows("2.xls").Activate)

My target: After excel finished all the updates in file 1 by macro 1, i would like macro 2 to excute the command i put in macro 1 in file 2. What can i do to tell excel to run in the different file instead of the one file that i wrote the codes on?I need a VBA code that can help me to achiece this. Can anyone help??

Thanks.

Answer 1

I might be missing something, but I threw together a sample (using Office 2007) and it seems to work for me. Here's what I did:

In 1.xlsm I have the following two macros:

Sub Macro1()

    Sheet1.Range("A1").Value = "This is Workbook 1"

End Sub

Sub RunOverwrite()

    Call Application.Run("Macro1")
    Call Application.Run("2.xlsm!Macro2")

End Sub

I have the following macro in a second workbook, called 2.xlsm:

Sub Macro2()

    Sheet1.Range("A1").Value = "This is Workbook 2"

End Sub

When I execute the RunOverwrite sub in the first workbook, it first writes "This is Workbook 1" in cell A1 of the first workbook ("1.xlsm"), and then writes "This is Workbook 2" in cell A1 of the second workbook ("2.xlsm").

Am I missing something from your original inquiry?

Answer 2

You could try rewriting your Macro to take in the name of a workbook as an argument. Then you just call it twice with the workbooks you want it to run on.

When you call it, it would look something like this:

Macro2("C:\Desktop\Workbook1.xls")
Macro2("C:\Desktop\Workbook2.xls")

In order for this to work, your macro would look something like this:

Sub Macro1(TargetBook as Workbook)
    ' Code goes here
End Sub