My sequence of Macros stops running after one of the macros closes another workbook

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
229
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Hope you're having a great Friday!

I am running a sequence of macros as follows:

Code:
Sub RunMacroSequence_Click()
    Call ClearContentsBelowRowTwo 'Macro1
    Call copyDataFromMultipleWorkbooksIntoMaster 'Macro2
    Call ExportOpsManPOReconExport 'Macro3
[B][COLOR=#b22222]    Call CloseWorkbookCCSWithoutSaving 'Macro4[/COLOR][/B]
    Call CloseWorkbookAPSWithoutSaving 'Macro5
    Call CloseWorkbookCognosLMSImporterWithoutSaving 'Macro6
    Call openwbPOReconLive 'Macro 5
End Sub

The sequence is interrupted when Macro 4 runs to close another workbook via...

Code:
Sub CloseWorkbookCCSWithoutSaving()
Workbooks("CCS Template2").Close SaveChanges:=False
End Sub

The file it's closing is a *.xltm file---if that makes any difference.

Is it possible to resume the sequence of macros after the VBA in one of the macros closes another workbook?

Also, I am interested to know why this happens: is this happening because the workbook I'm closing becomes active prior to closing, thus stopping the macro sequence from completing?

If you would help me with this, I'd be very grateful :)

Kind regards,

Doug.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Is Workbooks "CCS Template2" the one running the code?
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
229
Office Version
  1. 365
Platform
  1. Windows
Is Workbooks "CCS Template2" the one running the code?

Hi Nemmi69,

Thanks for replying.
The VBA code is running from a workbook called ReportImporter (which runs 7 macros in sequence below).

Code:
Sub RunMacroSequence_Click()
    [COLOR=#0000cd]Call ClearContentsBelowRowTwo 'Macro1
    Call copyDataFromMultipleWorkbooksIntoMaster 'Macro2
    Call ExportOpsManPOReconExport 'Macro3[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookCCSWithoutSaving 'Macro4[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookAPSWithoutSaving 'Macro5
    Call CloseWorkbookCognosLMSImporterWithoutSaving 'Macro6[/COLOR]
   [COLOR=#006400] Call openwbPOReconLive 'Macro 7[/COLOR]
End Sub

Macros 4 to 6 in ReportImporter close workbooks that ran earlier on i.e., in order to generate reports that ReportImporter process and export to file via Macros 2 to 3 in the above list i.e.,


  • Macro 4: closes CCS Template2.xltm
  • Macro 5: closes APS Template1.xltm
  • Macro 6: closes Cognos LMS importer1.xltm

Then the last macro ('Macro7 in the above list) opens a master workbook which parses and processes many streams of data.

To clarify:
The sequence of macros running within ReportImporter stops running at Macro 4. Macros 4 to 6 close external workbooks that are open within the same excel window as ReportImporter.

I would like to know if it is possible to close other workbooks but continue running the subsequent macros (5 to 7) within ReportImporter?

Kind regards,

Doug.
 

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Just wondering why you are using "Call" as should not be needed if all the macros are in the same workbook.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Doug

Why do you have separate subs to close workbooks?

Shouldn't the code to close workbooks be in the subs that open those workbooks?
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
229
Office Version
  1. 365
Platform
  1. Windows
Doug

Why do you have separate subs to close workbooks?

Shouldn't the code to close workbooks be in the subs that open those workbooks?

Hi Norie,

Thanks for replying also!

I have created a series of workbooks in which one runs then opens another, which then runs etc.. i.e., the first opens and automatically runs a sequence of macros culminating in a macro to open the next workbook.

E.g., workbook 1 opens workbook 2; then, workbook 2 has a Private sub to run a sequence of macros when it is opened.
The last macro in workbook 2 then opens workbook 3 etc.

As I was initially creating these workbooks, I encountered the same problem: if I called a macro to close a workbook, the next macro in the sequence wouldn't run. Therefore, I left these workbooks open.

If possible---for this last workbook (ReportImporter)---I wish to close off all the remaining open workbooks. It would be a nice to have to be able to do this, but my initial priority was to get the excel model running.

Kind regards,

Doug
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Doug

Is the code to run the sequence of macros in a separate workbook to the others?

When the code in one workbook finishes how is the next workbook opened and the code in it initiated?
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
229
Office Version
  1. 365
Platform
  1. Windows
Just wondering why you are using "Call" as should not be needed if all the macros are in the same workbook.

Hi Nemmi69,

I'm attempting to automate the running of macros in sequence within my workbooks so no button pressing is required. I'm also automating across a series of workbooks that do different data processing steps. Are you suggesting I create one sub in ThisWorkbook to run at workbook opening for all my macros within a workbook including the one to open the next workbook?

Anyhow, I did try combining the VBA code for closing all three workbooks within one sub...

Code:
Sub CloseWorkbookCognosLMSImporterWithoutSaving()
[COLOR=#0000cd][B]Workbooks("Cognos LMS Importer1").Close SaveChanges:=False[/B][/COLOR]
Workbooks("APS Template1").Close SaveChanges:=False
Workbooks("CCS Template2").Close SaveChanges:=False
End Sub

The result was an error message with the debug highlighting the first line of code (in blue) which was the only line which ran and successfully closed Cognos LMS Importer1.xltm

So the problem seems to be the same whether I run within one macro or as 3 macros called consecutively.

Kind regards,

Doug.
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
229
Office Version
  1. 365
Platform
  1. Windows
Doug

Is the code to run the sequence of macros in a separate workbook to the others?

When the code in one workbook finishes how is the next workbook opened and the code in it initiated?

Hi Norie,

Each separate workbook (e.g., ReportImporter.xlsm) contains the following vba within the microsoft excel object - ThisWorkbook:
Code:
Private Sub Workbook_open
     Call [COLOR=#333333]RunMacroSequence_Click 'Macro1
[/COLOR]End Sub

When the ReportImporter is opened, this private sub runs the below sequence of macros stored within modules in ReportImporter
Code:
[COLOR=#333333]Sub RunMacroSequence_Click()
[/COLOR][COLOR=#0000cd]    Call ClearContentsBelowRowTwo 'Macro1
    Call copyDataFromMultipleWorkbooksIntoMaster 'Macro2
    Call ExportOpsManPOReconExport 'Macro3[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookCCSWithoutSaving 'Macro4[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookAPSWithoutSaving 'Macro5
    Call CloseWorkbookCognosLMSImporterWithoutSaving 'Macro6[/COLOR]
    [B][COLOR=#006400]Call openwbPOReconLive 'Macro 7[/COLOR] 
[/B][COLOR=#333333]End Sub[/COLOR]

To clarify, there is no central workbook where my macros are stored: they all run from modules within the active workbook. The close macros aren't stored within the workbook I'm trying to close; rather, they are in ReportImporter.xlsm. That is why I wonder if the close macros within ReportImporter are activating the workbooks they are closing, and closing that workbook without then reactivating the ReportImporter.xlsm workbook?

To answer your last query: the next workbook is opened via the last macro in the sequence e.g., Call openwbPOReconLive 'Macro 7. The next workbook "POReconLive.xlsm" contains the same private sub within the excel object "ThisWorkbook" setting off its own macro sequence.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Have you tried using a separate workbook to start the sequence by simply opening the first book in the sequence?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

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