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>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,999
Messages
5,508,686
Members
408,690
Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top