how to continue original workbook macro after calling other workbook macro?

[Bendo]

Hi all,

I have several spreadsheets with macros. There will be many more.

I need to have a master spreadsheet that calls a slave sheet in a different workbook and runs a macro in that sheet, then returns to the master spreadsheet and then continues in the master sheet macro to the second slave sheet in a second workbook and run a macro and return to master macro and run next line, etc. etc.

The problem is that I can call to another sheet but once the sheet is finished it does not return to the master or it returns to the master but starts at the top of the master macro again. Looping forever.

How to continue where it left off with the master macro after returning to it from another macro?

Below is what I have. I must be missing something. Any cluesticks appreciated. Everything stops after the first macro.


Sub Master000()
Application.WindowState = xlMinimized
ChDir _
"D:\Data\001"
Application.Run "'D:\Data\001\go-001a.xls'!slave"

ChDir _
"D:\Data\002"
Application.Run "'D:\Data\002\go-002a.xls'!slave"

ChDir _
"D:\Data\003"
Application.Run "'D:\Data\003\go-003a.xls'!slave"


End Sub

[Bob Phillips]

That should continue fine. Something must be happening in those macros to cause the behaviour that you mention.

[Jan Karel Pieterse]

Just a question on application design:
Why have a macro in all of those files?
Instead, I'd have that 'slave' macro in just one workbook and call it on each of the files. Of course you'd have to adjust the code to work on a specific file, but that's easy.

[Bendo]

Thanks guys,

Ok XLD, I'll look into the macro that is running and see whether something is causing it to stop. I'll simplify the process on a test session and see how I go.

Jan, even though the name of the macro is the same in each of the files, The logic is different. However a lot is similar within, so naming it the same helps me when debugging. I'm going to have a lot more files so that is why a master macro.

Certain macros need to run on certain days, four on one day, six on the next including 3 from the first day, five the next day with two that same as previous days. Gets a pain in the neck running several every day. Just want one click to call different macros on different days.

I'll report back once I've simplified it and see the results. Thanks again.