Master Macro ends without completing all Application.Run tasks (Excel 2003)

[Simon3333]

This is a bit long winded but hopefully makes some form of sense...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Background<o></o>
<o></o>
Every month, my team are required to distribute 7 excel based Management Information reports (via creating a new tab in 7 separate workbooks).<o></o>
<o></o>
The data for the 7 reports originates from another 4 separate Workbooks.<o></o>
<o></o>
Until recently the job was done manually (i.e. updating pivots, copy, paste, etc...).<o></o>
<o></o>
I have attempted to automate the process, and have got to the point where the 4 Data Workbooks contain sufficient marcos to complete the monthly reports relevant to the data contained in each Data workbook.<o></o>
<o></o>
Each of the 4 Data workbooks contains its own Master Macro, which runs between 2 and 4 macros depending on which Workbook is being worked on. <o></o>
<o></o>
None of the marcos are particularly complicated (updating pivots, open an existing workbook, creating a new tab, copy, paste, format, save, close kind of thing) and my VBA ability is honestly little more than tweaking my recorded macros through trial and error until the required task gets done. <o></o>
<o></o>
Now my problem....<o></o>
<o></o>
I am currently trying to create a Master Master Macro so to speak, which will allow people to click one button to complete all 7 MI reports.<o></o>
<o></o>
To do this, I have created a new workbook (short name MMRM) and written 4 macros to open each of the 4 Data Workbooks and run the embedded Master Macros.<o></o>

Then, still in MMRM workbook, I have written the Master Master Macro, to run the 4 aforementioned macros located in the MMRM workbook.<o></o>
<o></o>
I have assigned the Master Master Macro to a button...When clicked, the first of the 4 Macros runs fine, but then instead of going on to run the next macro, the Master Master Macro ends (not stalls...ends...there is no error message and all cells become active again...).<o></o>
<o></o>
When I run the Macros individually in MMRM, each of them works perfectly and the corresponding MI reports get created....<o></o>
<o></o>
The code for the Master Master Macro is pasted below.

Sub UpdateAll()
'
' UpdateAll Macro
' Macro recorded 06/08/2010 by Simon C
'
Sheets("Sheet1").Visible = True
Application.Run "'Monthly Monitoring Reports Master.xls'!RunMM"
Application.Run "'Monthly Monitoring Reports Master.xls'!RunTR"
Application.Run "'Monthly Monitoring Reports Master.xls'!RunLD"
Application.Run "'Monthly Monitoring Reports Master.xls'!RunIM"
Sheets("Sheet1").Visible = False
Sheets("Monthly Monitoring Reports").Select
Range(A1).Select
'
End Sub

<o></o>
<o></o>
As all the other macros run normally when looked at individually, I haven't added their code, in case my issue a well know problem with an easy fix. Any ideas? <o></o>
<o></o>
Thanks. <o></o>

 

[ColinKJ]

Hi Simon,

Am I right in thinking each of the 4 Data Workbooks contain their own macros which AutoRun on Open.

Colin

 

[Simon3333]

Hi Colin,

The Macros in the Data Workbooks don't actually AutoRun when the workbooks are opened.

This is because the workbooks are opened on a number of occasions within any given month. One of my macros creates a new tab in the corresponding MI report Workbook with a name based on the current month - AutoRun would therefore cause the marco to bug if the Data Workbook was opened twice in the same month i believe....

Maybe it would be benifical if I gave a little more code, so people could follow how I have structured things....

So you can already see the Master Master Macro in the first post...below is the macro run via the first Application.Run (i.e. macro called RunMM)...

Sub RunMM()
'
' RunMM Macro
' Macro recorded 06/08/2010 by Simon C
'
Range("J4").Select
    Workbooks.Open Filename:= _
        "R:\xxxxxxx\Monitoring Master .xls", _
        UpdateLinks:=0
    Sheets("Run Button").Visible = True
    Sheets("Sheet1").Visible = True
    Sheets("Run Button").Select
    Application.Run _
        "'R:\xxxxxxx\Monitoring Master .xls'!UpdateAllMonitoringReportsSC"
    Windows("Monthly Monitoring Reports Master.xls").Activate
    Sheets("Sheet1").Select
'
End Sub

The UpdatedAllMonitoringReportsSC macro runs as follows:

Sub UpdateAllMonitoringReportsSC()
'
' UpdateAllMonitoringReportsSC Macro
' Macro recorded 05/08/2010 by Simon C
'
'
    Application.ScreenUpdating = False
    Sheets("Sheet1").Select
    Application.Run "'Monitoring Master .xls'!DATEandPIVOTS"
    Application.Run "'Monitoring Master .xls'!OverdueAnnualReviewsSC"
    Application.Run "'Monitoring Master .xls'!OverdueTenancySC"
    Application.Run "'Monitoring Master .xls'!OverdueAccountsSC"
    Sheets("Run Button").Visible = False
    Sheets("Sheet1").Visible = False
    Sheets("Use this tab!").Select
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Windows("Monthly Monitoring Reports Master.xls").Select
 
End Sub

I can go even further down into the code for these macros if needed.

Each of the Macros is the MMRM Workbook are set up in a similar fashion, and so are all the Macros across the 4 Data Workbooks.

Simon

 

[ColinKJ]

Simon,

It looks as if when you pass control to the "Monitoring Master .xls'!UpdateAllMonitoringReportsSC" in ""R:\xxxxxxx\Monitoring Master .xls", _
UpdateLinks:=0" you are loosing control when it closes itself.

"ActiveWorkbook.Close"

Have you tried stepping through the whole routine to see were exactly it stops.

Colin

 

[ColinKJ]

Simon,

You could try taking out the "ActiveWorkbook.Close" from "UpdateAllMonitoringReportsSC"

Then in "RunMM" after "Application.Run _
"'R:\xxxxxxx\Monitoring Master .xls'!UpdateAllMonitoringReportsSC"

Put

"Monitoring Master .xls .Close

Colin

 

[Simon3333]

Hi Colin,

I have made the changes you suggested and everything is now working!

Many thanks for your help!!

Simon