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

Simon3333

New Member
Joined
Aug 10, 2010
Messages
3
This is a bit long winded but hopefully makes some form of sense...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Background<o:p></o:p>
<o:p></o:p>
Every month, my team are required to distribute 7 excel based Management Information reports (via creating a new tab in 7 separate workbooks).<o:p></o:p>
<o:p></o:p>
The data for the 7 reports originates from another 4 separate Workbooks.<o:p></o:p>
<o:p></o:p>
Until recently the job was done manually (i.e. updating pivots, copy, paste, etc...).<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Now my problem....<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>

Then, still in MMRM workbook, I have written the Master Master Macro, to run the 4 aforementioned macros located in the MMRM workbook.<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
When I run the Macros individually in MMRM, each of them works perfectly and the corresponding MI reports get created....<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Thanks. <o:p></o:p>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Simon,

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

Colin
 

Simon3333

New Member
Joined
Aug 10, 2010
Messages
3
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)...

Code:
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:

Code:
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

Well-known Member
Joined
Jan 27, 2009
Messages
983
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

Well-known Member
Joined
Jan 27, 2009
Messages
983
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

New Member
Joined
Aug 10, 2010
Messages
3
Hi Colin,

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

Many thanks for your help!!

Simon
 

Watch MrExcel Video

Forum statistics

Threads
1,133,274
Messages
5,657,780
Members
418,413
Latest member
Radoslaw Poprawski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top