VBA Code req to close all workbooks

  • Thread starter Thread starter Neil Atkinson
  • Start date Start date
N

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
 
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.
******************************************
 
You might also want to test for Personal.xls, you probably don't want to
close that.
 
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
'<<====================
 
Back
Top