[Anonymous]

Here's a routine I found on the web and modified it for my need with Boston. Feel free to alter further for your need.

I call the procedure like this where I have a public variable for common locations I use which among them is LocationReportSort, etc. I also log all of my steps to a script log. You can take out those pieces as needed.
 

        'Sort InputFile A to Z on Account Name column header at "G1"
            Excel_SortColumn LocationReportSort, LocationReportFilter, "G1", "AZ"

'=== Open a XLSX with Excel without Excel visible, Sorts by Column Specified, And Saves Updated File To New Location ===
'=== UPDATED: 07/08/2020 ===

    Function Excel_SortColumn(LocationToSort As String, LocationDestinationFile, Sort_Column As String, SortByOptionChoose_AZ_or_ZA As String) As Boolean
        
        'Log Note
            Note = "Excel_SortColumn"
            LogNote "PROCEDURE: " & Note
                    
        'Determine if Already Completed
            If Dir(LocationDestinationFile) <> "" Then Exit Function
            
ProcRetry:
        'Setup Error Handling
            On Error GoTo ErrorHandler
            
        'Setup Local Variables
            Dim excelApp As Object 'Excel Application
            Dim InputFileName As String
            Dim InputFile As String

        'Set InputFile Name to Use
            InputFileName = Dir(LocationToSort)
            InputFile = LocationToSort & InputFileName
        'Verify Files Existence
            If Dir(InputFile) = "" Then Err.Raise 10000, Note, "ERROR: File to Sort Doesn't Exist"
        
        'Set ExcelApp to Excel Application
            Set excelApp = CreateObject("Excel.Application")
        'Use ExcelApp to Open File
            excelApp.Workbooks.Open (InputFile)
        'Sort WorkBook from Column A1 to End by value set with sort_column variable
            If SortByOptionChoose_AZ_or_ZA = "AZ" Then
                'Sort Smallest #/Newest Date/A to Z first
                    excelApp.ActiveWorkbook.ActiveSheet.Range("A1").Sort excelApp.ActiveWorkbook.ActiveSheet.Range(Sort_Column), , , , , , , 0
            Else
                'Sort Largest #/Oldest Date/Z to A first
                    excelApp.ActiveWorkbook.ActiveSheet.Range("A1").Sort excelApp.ActiveWorkbook.ActiveSheet.Range(Sort_Column), , , , , , , 1
            End If
        'Save Updated File After Sorting Completed
            excelApp.ActiveWorkbook.Save
        'Close Excel WorkBook
            excelApp.ActiveWorkbook.Close False
            DoEvents: Wait 0.2
        'Quit Excel Application
            excelApp.Quit
            
        'Set Old Folder Name variable
            FolderNameSource = Right(LocationToSort, Len(LocationToSort) – (InStrRev(LocationToSort, ProjectName) + Len(ProjectName)))
            FolderNameSource = Replace(FolderNameSource, "", "")
            
        'Set New Folder Name variable
            FolderNameDestination = Right(LocationDestinationFile, Len(LocationDestinationFile) – (InStrRev(LocationDestinationFile, ProjectName) + Len(ProjectName)))
            FolderNameDestination = Replace(FolderNameDestination, "", "")
            
        'Set Destination File to InputFile
            DestinationFile = Replace(InputFile, FolderNameSource, FolderNameDestination)
            
        'Copy Sorted File to Next Location
            FileCopy InputFile, DestinationFile
            DoEvents: Wait 0.2
    
        'Procedure Successful
            Excel_SortColumn = True
        
ProcExit:
        'Exit Function
            Exit Function
            
ErrorHandler:
        'Log Error and Shutdown script if ErrorNumber 10000 or greater
            LogError
        'Id DebugMode On, pause script to review error
            If DebugMode Or TestMode Then Stop: Resume
        'Email Failure & Shutdown Script
            EmailFailure
            
    End Function