Sort Columns Titles

[orlmor]

I have a macro who reads CSV files (22 in this test, but they are more) and put data starting on column 9 ("I").
I sort those columns using copy paste transpose, but I don't like this, I'm looking for a better way to sort the titles.
My sort code is:

Sub SortTitles()
    Range("I1:AD1").Copy
    Range("AF1").PasteSpecial Paste:=xlPasteAll, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    With ActiveWorkbook.Worksheets("Global").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("AF1:AF22"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("AF1:AF22")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Copy
    Range("I1").PasteSpecial Paste:=xlPasteAll, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Columns("AF").Delete xlToLeft
End Sub

 

[Noodleski]

Hi orlmor,

just so you'd know: excel offers a functionality to sort columns. It's under the sort feature => options => left to right.

I ran this throught the VBA recorder and got this:

Sub Macro1()
'
' Macro1 Macro

    ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add Key:=Range("B2:D2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Blad1").Sort
        .SetRange Range("B2:D5")
        .Header = xlYes
        .MatchCase = False
        .Orientation = [B]xlLeftToRight[/B]
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[orlmor]

Thanks a lot Noodleski it's what I was looking for.

 

[Noodleski]

Glad to hear.