Start Running an Excel Macro in another workbook while closing original workbook

Viewed 30

I have 2 Spreadsheets, for testing called Red and Blue. I need to run a macro in Red, which opens Blue, Closes Red, Does some stuff in Blue, then Reopens red and does some stuff in Red. I can get The macro in Red to Open blue and run a macro in Blue, but if that macro in Blue closes Red, it stops and won't reopen Red.

This is the Macro in Red:

Sub Macro1()

Workbooks.Open "C:\Users\Carey.Zimmer\Documents\Practice\September 2022\BookHopping\Blue.xlsm"
Range("B2") = "Macro 1 Run Successfully in Blue"
Workbooks("Red.xlsm").Activate
Range("B2") = "Macro 1 Run Successfully in Red"
Application.Run ("'Blue.xlsm'!Macro2")

End Sub

And this is the Macro in Blue:

Sub Macro2()

Workbooks("Red.xlsm").Activate
Range("B3") = "Macro 2 Step 1 Run Successfully in Red"
Workbooks("Blue.xlsm").Activate
Workbooks("Red.xlsm").Close SaveChanges:=True
Workbooks("Blue.xlsm").Activate
Range("B3") = "Macro 2 Run Successfully in Blue"
Workbooks.Open ("C:\Users\Carey.Zimmer\Documents\Practice\September 2022\BookHopping\Red.xlsm")
Range("B4") = "Macro 2 Step 2 Run Successfully in Red"

End Sub

I start with both workbooks empty and only Red open when I run Macro1.

I end with Only Blue Open, with Macro1 run successfully in both, and only step 1 of Macro2 run successfully in Red.

0 Answers
Related