Auto Save and Close all open files

[julhs]

This should be as simple simple as can be BUT!!
I have 2 workbooks open; I’m in Book 1 making it the “Active Book”
In Book1 I have testing module that when run is supposed to save and close ALL workbooks.

Sub Close_All_Files_Save()
'Close all open workbooks and save
Dim wb As Workbook
'Loop through each workbook
For Each wb In Application.Workbooks
'Close the workbooks and save changes
wb.Close SaveChanges:=True
Next wb
End Sub

However, it ONLY saves and closes Book1 (the Active Workbook) BUT not Book2 as well.
I have also tested this (run from Book1)

Sub Close_All_Files_Save()
'Close all open workbooks and save
Dim wb As Workbook
'Loop through each workbook
For Each wb In Application.Workbooks
'Prevent the workbook that contains the code from being closed
If wb.Name <> ThisWorkbook.Name Then
'Close the workbook and save changes
wb.Close SaveChanges:=True
End If
Next wb
End Sub

It closes and saves Book2 and leaves Book1 open (as it is coded to do)

Question is WHY wouldn’t the first code; to save and close BOTH books fully work?
Help as ever is greatly appreciated!!

 

[julhs]

I inserted a break point at the “Call” event in “Workbook_BeforeClose”, activated “Immediate” and “Locals” windows and stepped through the sequence.
Without supplying you with all the step by step results of “Immediate” and “Locals” windows, I'm not sure I will provide you with the answer to question that you are alluding to?

But long and short of stepping through it was;
Immediate window resulted in blank
Locals window resulted in blank
Blank instance of Excel remained

 

[rlv01]

If I were going to do it, I would do it all in Sub Workbook_BeforeSave and not use a call to Sub Close_All_Files_Save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wb As Workbook
    
    If SaveAsUI = False Then
        'Save other workbooks
        For Each wb In Application.Workbooks
            If wb.Name <> Me.Name Then
                wb.Close SaveChanges:=True
                DoEvents
            End If
        Next wb
        
        'Save this workbook
        Application.EnableEvents = False
        Me.Save
        DoEvents
        Application.EnableEvents = True
    
        'Quit Excel
        Application.Quit
    End If
End Sub

 

[julhs]

I did try using the existing “Sub Close_All_Files_Save” code as it was in “ThisWorkbook” module, without success.
But see that your currant suggestion has some significant modifications.
Going to have to leave it until tomorrow to run it.
Just out of interest, where on the planet are you located?

 

[julhs]

Taken me time to research “SaveAsUI” and test out and experiment with your last suggestion, alongside trying other approaches.
BEFORE I go any further; I don’t think I’ve helped YOU in helping ME with what I wanted to do and how I was intending on implementing it.
At the onset of the post I neglected to state that ultimately I wanted to close & save all open Workbooks when the primary “Workbook” application Red X (Close/exit button)“ was selected.
I appreciate this changes things significantly!!!
So with that said, what would the syntax be to Save & Close all open Workbooks when the Red X of the primary Workbook is selected/clicked?
Your latest suggestion clearly works; but only when I use the “Save” button on the primary Workbook, but NOT by selecting the Red X on the primary Workbook.
I cobbled together the below after research and testing (it is placed in a standard model)
When it’s run via F5 it does want I want; saves & closes all Open workbooks and NO other instances of Excel are active.
But when it is triggered by selecting/clicking the the Red X (Close/exit button) on the “Primary Workbook” it is leaving a blank instance of Excel still open.
I’m now so confused and frustrated with all this, that I feel simplest thing is to attach a Save/Close Sub to a command button (which what I was trying to avoid)

Sub Auto_Close()
  Dim wb As Workbook
  Dim pwb As Workbook
     'Loop through each workbook
   For Each wb In Application.Workbooks
     'Prevent the workbook that contains the code from being closed
  If wb.Name <> ThisWorkbook.Name Then
     'Close the other workbooks and save changes
   wb.Close SaveChanges:=True
  End If
 Next wb
     Application.Quit
  Set pwb = ThisWorkbook 'just added
    With pwb
      .Close SaveChanges:=True
  End With
End Sub