VBA code to close workbook

The workbook's Close method closes the Set workbook, for example ActiveWorkbook. This page discusses several ways to to this:

SaveChanges - to save or not to save changes

The SaveChanges argument specifies whether changes should be saved. If there are no changes to the workbook, this argument is ignored and the workbook just closes. If there are changes to the workbook and the workbook appears in other open windows, this argument is also ignored.

dialog asking the user if he wants to save the changes

SaveChanges not specified - ask the user

If SaveChanges is not specified the user is asked if he wants to save the changes.

Save and close workbook

If SaveChanges is set to True, changes are saved to the workbook. If there is not yet a file name associated with the workbook, then FileName is used. If Filename is omitted, the user is asked to supply a file name.

Dim wb As Workbook: Set wb =
wb.Close SaveChanges:=True

Close workbook without saving

Dim wb As Workbook: Set wb =
wb.Close SaveChanges:=False
close save changes

Close all workbooks

Closing all workbooks leaving it up to the system whether or not to ask is changes need be saved is simple.

Workbooks.Close

Close all without saving using Code VBA is two steps:

  1. Insert For each loop over the workbooks
  2. Insert the appropriate close action
Dim wb As Workbook
For Each wb In Application.Workbooks
    wb.Close SaveChanges:=False
Next wb

Filename

Save changes under this file name. This only works if the following consitions are met:

  1. There must be changes in the workbook
  2. SaveChanges:=True
  3. Extension / File Save As type must be same as of the workbook being saved. If not this will result in Error 1004
Dim wb As Workbook: Set wb =
Dim strFilename As String: strFilename =
wb.Close Filename:=strFilename

RouteWorkbook

If the workbook doesn't need to be routed to the next recipient (if it has no routing slip or has already been routed), this argument is ignored. Otherwise, Microsoft Excel routes the workbook according to the value of this parameter. If set to True, the workbook is sent to the next recipient. If set to False, the workbook is not sent. If omitted, the user is asked whether the workbook should be sent.

Run some code before closing the Workbook

To run code before closing the Workbook put it under the Workbook_BeforeClose event.

Example uses for Workbook_BeforeClose
  • You can add Application.CutCopyMode = False to prevent the warning 'Do you want to save the clipboard' in case you previously did a copy action.
  • Another use of the event would be to set ThisWorkbook.Saved = True so that you don't get the question 'Do you want to save the changes you made to ...'