Tuesday, 19 February 2013

VBA code to close all Excel workbooks except the active workbook

VBA code to close all Excel workbooks except the active workbook:

If you had worked in Excel with several workbooks at a time, you might have came across a question like this.

How to close all other workbooks except the active workbook in one shot instead of going to each workbook and close ?

Microsoft Excel Does'nt provide a direct way to close several workbooks at a time.

But we can use VBA codes to accomplish this task.

How :

  1. Select the workbook you wish to retain
  2. Go to VBE
  3. Add one Module
  4. Copy paste the below sub procedure to a module in your code
  5. Run the Macro Whenever you want to close other workbooks
  6. Remember to Save the file as MacroEnabled
Sub CloseAllSheetsExActive()
Dim WBs As Workbook
For Each WBs In Application.Workbooks
If Not WBs.Name = ThisWorkbook.Name Then WBs.Close (False)
Next WBs
End Sub

Note : Macro will close all the workbooks ignoring all the changes you have made without asking whether to save it or not. If you use TRUE instead of FALSE after close method, it will save all the changes without asking. If you don't use TRUE or FALSE, a dialog box will show to ask whether you wish to save or close without saving.