How to Automatically Sort an Excel Table Before Closing Using VBA
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.

- 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.
Ensure you save your workbook as a Macro-Enabled Workbook (.xlsm) and create a backup of your data before running any new VBA scripts.
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.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.
In the Project Explorer pane on the left side of the screen, locate your current workbook and double-click on 'ThisWorkbook'.
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
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.
Close the VBA editor and save your file as an Excel Macro-Enabled Workbook (.xlsm). Close the file to test the automatic sorting.

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. Open your workbook in WPS Spreadsheet: Launch WPS Spreadsheet and open the file where you want to add the sorting macro.
- 2. Navigate to the Developer Tab: Click on the 'Tools' or 'Developer' tab on the top ribbon.
- 3. Open the Visual Basic Editor: Click the 'Macro' or 'Visual Basic Editor' icon to open the coding environment.
- 4. Insert the BeforeClose Event: Double-click 'ThisWorkbook' in the Project window and paste your sorting macro.
- 5. Save the Document: Save the document as a macro-enabled format (.xlsm) to retain the automation for future use.

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.




