VBA Code req to close all workbooks

[Neil Atkinson]

Hi I need a code that closes all open workbook when a workbook I have
created opens. The workbook is quite large and can cause memory errors
if lots of other workbooks are open at the same time. Probably would be
best if all workbooks when closing are auto saved.

Many Thanks in advance
Neil

 

[Norman Jones]

Hi Neil,

Try:

'====================>>
Sub Workbook_Open()
Dim WB As Workbook

For Each WB In Application.Workbooks
If WB.Name <> ThisWorkbook.Name Then
WB.Close SaveChanges:=True
End If
Next WB

End Sub
'<<====================

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module):

******************************************
Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
******************************************

 

[Neil Atkinson]

Many Thanks I had forgot how simple that is...

Rgds
Neil

 

[Bob Phillips]

You might also want to test for Personal.xls, you probably don't want to
close that.

 

[Norman Jones]

Hi Bob,

A good suggestion.


Neil,

Should you wish to keep your Peronal.xls workbook open, as per Bob's
suggestion, then try this amendment:

'====================>>
Sub Workbook_Open()
Dim WB As Workbook

For Each WB In Application.Workbooks
If WB.Name <> ThisWorkbook.Name _
And LCase(WB.Name) <> "personal.xls" Then
WB.Close SaveChanges:=True
End If
Next WB

End Sub
'<<====================