Automatic Sort when closing Excel

[_seadogg]

I have to send a workbook each day. I don't need to, but I would like to sort the data alphabetically starting with cell A2. There are a total 4 columns of data. I know how to sort the data, the problem is I always forget to sort it before sending. Is it possible for Excel to sort the data when I hit the "X" to close the app?

Using Windows 10, Excel 365 build 2405

Thank you in advance for your expertise.

[#2 · Accepted solution]

@_seadogg 

You can automate the sorting of data in Excel when you close the workbook by using VBA (Visual Basic for Applications). Without using VBA, there is no direct way to automate sorting when closing the workbook, so far i know.This involves creating a macro that will run automatically when the workbook is closed. Here are the steps to set this up:

Step-by-Step Guide

1. Open the Visual Basic for Applications (VBA) Editor:
• Press Alt + F11 to open the VBA editor.
2. Insert Code in ThisWorkbook Module:
• In the Project Explorer window (usually on the left side of the VBA editor), find your workbook. It will be listed as "VBAProject (YourWorkbookName)".
• Double-click on ThisWorkbook to open the code window for the workbook.
3. Add the Workbook_BeforeClose Event:
• In the code window, paste the following VBA code:

Vba Code is untested backup your file.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    
    With ws
        .Range("A2:D" & .Cells(.Rows.Count, "A").End(xlUp).Row).Sort Key1:=.Range("A2"), _
            Order1:=xlAscending, Header:=xlNo
    End With
End Sub

Explanation of the Code

• Private Sub Workbook_BeforeClose(Cancel As Boolean): This subroutine runs automatically before the workbook closes.
• Dim ws As Worksheet: Declares a variable for the worksheet.
• Set ws = ThisWorkbook.Sheets("Sheet1"): Sets the variable to the specific worksheet you want to sort. Change "Sheet1" to the name of your worksheet.
• With ws: Begins a block to work with the specified worksheet.
• .Range("A2:D" & .Cells(.Rows.Count, "A").End(xlUp).Row).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo: Sorts the range from A2 to the last used row in column D, based on the values in column A, in ascending order.

Important Notes

• Make sure to save your workbook as a macro-enabled workbook (.xlsm).
• Test the macro to ensure it sorts the data correctly before relying on it for daily use.
• You can customize the range and sort key as needed for your specific data structure.

Testing the Macro

1. Save Your Work: Ensure all your work is saved.
2. Close the Workbook: Close the workbook to trigger the Workbook_BeforeClose event.
3. Reopen the Workbook: Reopen it to verify that the data has been sorted as expected.

By following these steps, your data will be automatically sorted every time you close the workbook, ensuring you don't forget to do it manually. The text, steps and code were created with the help of AI. Consider checking important information.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

Thank you so much! It work flawlessly! Cheers! @NikolinoDE