Excel formula/VBA to sort table

How to sort a table? I have a dataset (column A - G) that I would like to sort to show only columns A, C, E, B in the above order. Is there an excel formula / VBA formula that I can look into to automate?

Choose Columns

Excel Formula

Microsoft 365 2022

=CHOOSECOLS(A1:G11,1,3,5,2)

or by using an array with row separators

=CHOOSECOLS(A1:G11,{1;3;5;2})

or using the array with the correct column separators
or a combination of the previous two.

Microsoft 365 2021

=LET(d,A1:G11,c,{1,3,5,2},
    r,SEQUENCE(ROWS(d)),
INDEX(d,r,c))

• Depending on your decimal separator, the array column separator (the comma(s) in the array {1,3,5,2}) may be different.
  So far, I have encountered {1.3.5.2}, {1\3\5\2}, or {1@3@5@2}.

• To make it work on any locale, you could use TRANSPOSE (there is no TOROW yet) with row separators:

=LET(d,A1:G11,rc,{1;3;5;2},
    r,SEQUENCE(ROWS(d)),c,TRANSPOSE(rc),
INDEX(d,r,c))

Older Versions

=INDEX(A1:G11,ROW(A1:G11)-ROW(A1)+1,{1,3,5,2}) 

If your data starts in row 1, you can simplify with:

=INDEX(A1:G11,ROW(A1:G11),{1,3,5,2})

• The same as in 2021, you can make both work for any locale by using TRANSPOSE({1;3;5;2}).

VBA

Copy Including Formulas and Formatting

Sub CopyColumns()

    Dim Cols(): Cols = Array(1, 3, 5, 2)

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim srg As Range: Set srg = ws.Range("A1").CurrentRegion
    
    Dim dfCell As Range: Set dfCell = ws.Range("I1")
    
    Dim Col
    
    For Each Col In Cols
        Debug.Print "Copying """ & srg.Columns(Col).Address(0, 0) _
            & """ to """ & dfCell.Address(0, 0) & """..."
        srg.Columns(Col).Copy dfCell
        Set dfCell = dfCell.Offset(, 1)
    Next Col
 
    MsgBox "Columns copied.", vbInformation

End Sub

Copy Values Only (Using a Function)

The Function

Function ChooseColumns( _
    ByVal SourceRange As Range, _
    ByVal ChosenColumns As Variant) _
As Variant
    
    Dim rCount As Long: rCount = SourceRange.Rows.Count
    Dim scCount As Long: scCount = SourceRange.Columns.Count
    
    Dim sData()
    
    If rCount * scCount = 1 Then
        ReDim sData(1 To 1, 1 To 1): sData(1, 1) = SourceRange.Value
    Else
        sData = SourceRange.Value
    End If
        
    Dim dcCount As Long:
    dcCount = UBound(ChosenColumns) - LBound(ChosenColumns) + 1
    
    Dim dData(): ReDim dData(1 To rCount, 1 To dcCount)
        
    Dim sCol, r As Long, dc As Long
    
    For Each sCol In ChosenColumns
        dc = dc + 1
        For r = 1 To rCount
            dData(r, dc) = sData(r, sCol)
        Next r
    Next sCol

    ChooseColumns = dData

End Function

The Sub (Using the Function)

Sub CopyColumnsUsingFunction()

    Dim Cols(): Cols = Array(1, 3, 5, 2)

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim srg As Range: Set srg = ws.Range("A1").CurrentRegion
    
    Dim Data(): Data = ChooseColumns(srg, Cols)
    
    Dim dfCell As Range: Set dfCell = ws.Range("I1")
    
    dfCell.Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
    
    MsgBox "Columns copied.", vbInformation

End Sub

List Separators

Sub ShowMySeparators()
    With Application
        Debug.Print "Alternate: " & .International(xlAlternateArraySeparator)
        Debug.Print "Column: " & .International(xlColumnSeparator)
        Debug.Print "Decimal: " & .International(xlDecimalSeparator)
        Debug.Print "List: " & .International(xlListSeparator)
        Debug.Print "Row: " & .International(xlRowSeparator)
        Debug.Print "Thousands: " & .International(xlThousandsSeparator)
    End With
End Sub

Results on My System

Alternate: @
Column: ,
Decimal: .
List: ;
Row: ;
Thousands: ,