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

[dougmarkham]

Hi everyone,

Hope you're having a great Friday!

I am running a sequence of macros as follows:

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...

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.

 

[nemmi69]

Think it would be best if one independant workbook controlled them all.

 

[RoryA]

Closing a workbook would only stop your macros from running further if you close the workbook with the code in it or if there is something like a Workbook_BeforeClose or _Deactivate event in the workbook you close that has an End statement in it.

 

[dougmarkham]

Have you tried using a separate workbook to start the sequence by simply opening the first book in the sequence?

My issue appears to be thus:

When I am running a sequence of macros using Call, or even if commands are placed in the same sub: If I use VBA to either open a workbook or close another workbook, then...

• The vba command next in sequence doesn't run

For instance, the last line of Macro 7'---that comes after the vba that opens my next workbook---commands excel to close ReportImporter.xlsm. In testing, this last line of code doesn't actually run; rather, excel opens the stated workbook and fails to move to the next line of code.

Sub openwb()    
    Dim sPath As String, sFile As String
    Dim wb As Workbook

    sPath = "S:\APS_Logistics\OPERATIONS MANAGER\"
    sFile = sPath & "PO Reconciliation Live.xlsm"

    Set wb = Workbooks.Open(sFile)
[COLOR=#b22222][B]    Workbooks("[/B][/COLOR][COLOR=#b22222][B]ReportImporter[/B][/COLOR][COLOR=#b22222][B].xlsm").Close SaveChanges:=False[/B][/COLOR]
End Sub

Originally Posted by Norie
Have you tried using a separate workbook to start the sequence by simply opening the first book in the sequence?

No I haven't tried that.
What's your idea behind that suggestion?

 

[dougmarkham]

Closing a workbook would only stop your macros from running further if you close the workbook with the code in it or if there is something like a Workbook_BeforeClose or _Deactivate event in the workbook you close that has an End statement in it.

Hi RoryA,

Thanks for chipping in.
This is what I assumed prior to this; however, the code is in the workbook that I'm operating not the workbook I'm closing, also, there are no Workbook_BeforeClose or _Deactivate events within any of my workbooks.

Kind regards,

Doug.

 

[Norie]

The idea is that you have a separate workbook that isn't in the 'sequence' but is used to 'kick' the sequence off.

 

[dougmarkham]

The idea is that you have a separate workbook that isn't in the 'sequence' but is used to 'kick' the sequence off.

I tried that and found I got Run-time error '9': Subscript out of range i.e., only when running the sequence from an outside book via...

Sub SetOffMacro()
Application.Run ("'ReportImporter.xlsm'!RUNMACROS_Click")
End Sub

Kind regards,

Doug

 

[Norie]

Doug

That suggests there could be problems in the code you are trying to run, specifically that things aren't being referenced correctly.

 

[dougmarkham]

Doug

That suggests there could be problems in the code you are trying to run, specifically that things aren't being referenced correctly.

Here is the code in question:

Sub copyDataFromMultipleWorkbooksIntoMaster()

Dim FolderPath As String, Filepath As String, Filename As String

FolderPath = "S:\APS_Logistics\OPERATIONS MANAGER\PO Reconciliation Creator\Ops manager exports B\"

Filepath = FolderPath & "*.xls*"
Filename = Dir(Filepath)

Dim lastrow As Long, lastcolumn As Long

Do While Filename <> ""
Workbooks.Open (FolderPath & Filename)

lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
[B][COLOR=#ff0000]ActiveSheet.Paste Destination:=Worksheets("Ops Man PO Recon Export").Range(Cells(erow, 1), Cells(erow, lastcolumn))[/COLOR][/B]

Filename = Dir

Loop
Application.DisplayAlerts = True
End Sub

The debugger highlights the line in red. The worksheet "Ops Man PO Recon Export" is the only tab in the workbook: ReportImporter.
How would one alter the references to enable an outside workbook to initiate the macro chain?

 

[Norie]

Which parts of the code are meant to apply to which workbook?

For example, in the problem line which workbook is ActiveSheet in and which is the sheet 'Ops Man PO Recon Export' in?

 

[dougmarkham]

Which parts of the code are meant to apply to which workbook?

For example, in the problem line which workbook is ActiveSheet in and which is the sheet 'Ops Man PO Recon Export' in?

Hi Norie,

The active workbook is ReportImporter which resides in: FolderPath = "S:\APS_Logistics\OPERATIONS MANAGER\PO Reconciliation Creator\Ops manager exports B\ReportImporter".

The only sheet in that workbook is Ops Man PO Recon Export. This macro extracts data from all excel files in the folder location: FolderPath = "S:\APS_Logistics\OPERATIONS MANAGER\PO Reconciliation Creator\Ops manager exports B\.

There are two workbooks in this folder: APS Recon to Export and CCS Recon to Export. The macro pulls data from both these files and combines them into one table in ReportImporter . Another macro then exports this data into a blank workbook and saves as.

Kind regards,

Doug.