VBA to Call Multiple Macros

[Pierce Quality]

Hi All,

I'm having an issue with calling multiple macros in VBA and was wondering if someone could offer me some insight.
In the code below, Macro1 is supposed to open a workbook in another directory, run the macro (WBMerge)stored in that other workbook (which merges worbooks in that directory then saves and closes that workbook), then another Macro ("WSMerge" which merges worksheets in the current workbook) that is stored in the current workbook.
The first two lines run great, but "WSMerge" wont execute (WSMerge works fine by itself).

Any help would be greatly appreciated, thanks!

Please Login or Register  to view this content.

[Norie]

Which workbook is WSMerge located in?

[Pierce Quality]

Which workbook is WSMerge located in?

Hello Norie, thanks for the reply!

The WSMerge macro is located in the same book that Macro1 is in.

Thanks!

[Norie]

What happens when WSMerge is called?

Is it ever called?

You can check that by putting a breakpoint (F9) on the line that calls it and then running Macro1.

If the code is't interrupted at the point it means that line is never reached, which could mean that there is something in WBMerge stopping code execution.

[Pierce Quality]

What happens when WSMerge is called?

Is it ever called?

You can check that by putting a breakpoint (F9) on the line that calls it and then running Macro1.

If the code is't interrupted at the point it means that line is never reached, which could mean that there is something in WBMerge stopping code execution.

Norie,
WSMerge doesn't appear to be getting called.
Here is the WBMerge macro, see anything that would be causing this?

Thanks!

Please Login or Register  to view this content.

[Norie]

Nothing that I can see but that code is opening a lot of other workbooks and it is possible that there's code in one of those workbooks causing the problem.

This is a bit of a long shot but might be worth a try, at the start of Macro1 disable events.

Please Login or Register  to view this content.

Don't forget to enable events at the end.

Please Login or Register  to view this content.

[Pierce Quality]

Nothing that I can see but that code is opening a lot of other workbooks and it is possible that there's code in one of those workbooks causing the problem.

This is a bit of a long shot but might be worth a try, at the start of Macro1 disable events.

Please Login or Register  to view this content.

Don't forget to enable events at the end.

Please Login or Register  to view this content.

Thanks Norie,
I ran the macro as you suggested and acheived the same results.
As for the code in the merged workbooks, here is a copy of the only other macro in that directory:

Please Login or Register  to view this content.

See anything in there?
This is quite perplexing!

Thanks!

[LJMetzger]

Try running 'WBMerge' by itself, and make sure it runs to completion and doesn't hang up somewhere.

If 'WBMerge' does not complete, then macro control will not return to your original macro and the 'WSMerge' line will never get called.

Lewis

[Pierce Quality]

Try running 'WBMerge' by itself, and make sure it runs to completion and doesn't hang up somewhere.

If 'WBMerge' does not complete, then macro control will not return to your original macro and the 'WSMerge' line will never get called.

Lewis

Hello Lewis, thanks for the reply!
The WBMerge runs to completion by itself.
Thanks!

[Norie]

If WBMerge runs to completion then WSMerge should be executed.

[Pierce Quality]

If WBMerge runs to completion then WSMerge should be executed.

lol thats exactly how I feel!

Just tried deleting the save and close commands at the end of WB Merge, and I started getting an error on WSMerge so that may be promising. Maybe something to do with the active sheet?

Here is the code:

Please Login or Register  to view this content.

Thanks!

[Norie]

What happens if you move WBMerge to the same workbook as Macro1 and WSMerge?

You should then be able to call both subs like this.

Please Login or Register  to view this content.

[Pierce Quality]

What happens if you move WBMerge to the same workbook as Macro1 and WSMerge?

You should then be able to call both subs like this.

Please Login or Register  to view this content.

Initally I wanted to merge the workbooks that are in a different directory to a master file but couldnt get it work across directories, so I ended up placing a dummy book in the same directory as the workbooks to be merged and running a data connection to it. Might have to re visit that plan I think!
lol

Thanks!

[Norie]

Having workbooks in different directories shouldn't make a difference, as long as you use the correct paths/filenames etc and keep track of everything.

[LJMetzger]

Deleted - bad answer. Sorry.

Lewis

[LJMetzger]

The classic Excel Macro Paradox.

File A starts a Macro.
File A opens File B which runs a Macro.
File A is waiting for File B to complete the File B Macro, so that File A can continue.
File A takes control when the File B Macro completes, ONLY IF FILE B IS STILL OPEN.
If File B closes itself, then Excel SHUTS DOWN ALL MACROS and File A never regains control.

A possible solution to your problem is:
a. Commenting out the 'Close' statements below.
b. Have File A close File B.

Possible long term fix:
Have File B close itself only if the File A Workbook is NOT OPEN.

See the attached Father/Son programs which demonstrate the situation.

Lewis

Please Login or Register  to view this content.

[Norie]

Another possible solution would be to have the code in one workbook.

Then the only workbooks being opened/closed would be those you are copying from, and perhaps the workbook you are copying to.

[Pierce Quality]

Having workbooks in different directories shouldn't make a difference, as long as you use the correct paths/filenames etc and keep track of everything.

This is true, maybe I was doing something wrong before.

Deleted - bad answer. Sorry.

Lewis

Thanks, no problem!

The classic Excel Macro Paradox.

File A starts a Macro.
File A opens File B which runs a Macro.
File A is waiting for File B to complete the File B Macro, so that File A can continue.
File A takes control when the File B Macro completes, ONLY IF FILE B IS STILL OPEN.
If File B closes itself, then Excel SHUTS DOWN ALL MACROS and File A never regains control.

A possible solution to your problem is:
a. Commenting out the 'Close' statements below.
b. Have File A close File B.

Possible long term fix:
Have File B close itself only if the File A Workbook is NOT OPEN.

See the attached Father/Son programs which demonstrate the situation.

Lewis

Please Login or Register  to view this content.

Thanks Lewis, I think thats the root cause of the of the problem. I ended up tweaking your code a little, the macro still wasnt getting called until I re activated the merge workbook:

Please Login or Register  to view this content.

Another possible solution would be to have the code in one workbook.

Then the only workbooks being opened/closed would be those you are copying from, and perhaps the workbook you are copying to.

This would be the ideal structure as I would like to only have one master file to work with, however at the time I could not get the workbook merge to work across directories, but only with a dummy book in the same directory (using different code than the current WBMerge of course).
I like where that idea is going though, so I will mark this thread as solved and start a new thread for that very purpose.

Thanks again everyone!