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.