VBA & Macro Problems

How to Automatically Sort an Excel Table Before Closing Using VBA

Ayan MasoodAyan Masood Nov 11, 2026 869 views

Question details

The user needs to automate the sorting of a four-column data range alphabetically by column A every time the Excel workbook is closed.

How to Automatically Sort an Excel Table Before Closing the Workbook
Product
Excel 365
Device & OS
not provided
Scenario
Managing a dataset that requires consistent alphabetical ordering upon exiting the file without relying on manual sorting.
Observed behavior
The data remains unsorted unless manually organized before closing; the goal is to trigger an automatic sort using VBA.
Before you start

Ensure you save your workbook as a Macro-Enabled Workbook (.xlsm) and create a backup of your data before running any new VBA scripts.

Solution 1Recommended

Use the Workbook_BeforeClose VBA Event

Implement a VBA macro triggered right before the workbook closes to sort the specified range automatically.

By utilizing the Workbook_BeforeClose event in VBA, you can force Excel to execute specific actions, such as sorting a table, immediately upon the user attempting to close the file. This ensures your data is always organized for the next session.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

2
Access ThisWorkbook Module

In the Project Explorer pane on the left side of the screen, locate your current workbook and double-click on 'ThisWorkbook'.

3
Paste the VBA Code

In the code window that appears, paste the following code: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") With ws .Range("A2:D" & .Cells(.Rows.Count, "A").End(xlUp).Row).Sort _ Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo End With End Sub

4
Customize the Code Parameters

Change 'Sheet1' to the actual name of your worksheet, and adjust 'A2:D' if your data spans different columns or starts at a different row.

5
Save as Macro-Enabled Workbook

Close the VBA editor and save your file as an Excel Macro-Enabled Workbook (.xlsm). Close the file to test the automatic sorting.

Use the Workbook_BeforeClose VBA Event
Enable Macros: Ensure that macros are enabled in your Trust Center settings, otherwise the sorting code will not execute when closing the workbook.
Efficiently Manage Macros in WPS Office

Automate Sorting with WPS Spreadsheet Macros

WPS Office fully supports VBA macros, allowing you to run automated scripts like sorting data before closing your workbooks seamlessly.

  1. 1. Open your workbook in WPS Spreadsheet: Launch WPS Spreadsheet and open the file where you want to add the sorting macro.
  2. 2. Navigate to the Developer Tab: Click on the 'Tools' or 'Developer' tab on the top ribbon.
  3. 3. Open the Visual Basic Editor: Click the 'Macro' or 'Visual Basic Editor' icon to open the coding environment.
  4. 4. Insert the BeforeClose Event: Double-click 'ThisWorkbook' in the Project window and paste your sorting macro.
  5. 5. Save the Document: Save the document as a macro-enabled format (.xlsm) to retain the automation for future use.
Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in VBA editor for creating and modifying sorting scriptsLightweight and runs complex macros efficiently without laggingFree to download and use for daily office automation tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my BeforeClose macro running when I exit Excel?

Macros might be disabled in your Trust Center settings, or the file was accidentally saved as a standard .xlsx instead of .xlsm. Additionally, if Excel crashes or is forcefully closed via Task Manager, BeforeClose events will not trigger.

Can I sort multiple columns automatically using VBA?

Yes, you can modify the VBA Sort method to include Key2 and Key3 parameters for secondary and tertiary sorting columns. Simply add them to the Sort command in your script.

Will this macro prompt me to save changes every time?

Yes, because the macro modifies the data by sorting it, Excel will prompt you to save before closing. If you want to suppress this prompt and save automatically, you can add 'ThisWorkbook.Save' on the line just before 'End Sub' in your VBA code.