Thread: How to write a macro to sort many columns from largest to smallest

SOLVED

Hi all,
Background: I am trying to sort columns AN-BQ by largest to smallest. Apparently, when you sort a column largest to smallest and then you do it to another column, the first column stays in tact. For example, lets say I sorted AN largest to smallest, and then I did the same to AO, and then to AP. It would not mess up column AN. It would look like this
AN AO AP
X X 10
X X 9
X X 8
X 10
X 9
X 8
10
9
8
Since there are a lot of columns, I don't want to click sort from largest to smallest a lot of times. I have to do this twice a month for work and I am trying to get a macro to do it. Does anyone have any suggestions? I think it might involve a loop. I am quite noobish at excel VBA.
I have uploaded an examle file. I cannot upload the real file due to sensitivity of information. In the sheet called original is the raw data. In the next sheet, only columns B and C are in the correct format. If I format the rest of the columns, it gets messed up. Also, my boss was the one that told me the columns would not be messed up if I sorted the other columns but so far I have not seen that to be true. If there a way to do that or is my boss batty?
Thank you all so, so much.

Thank you so much for helping, It is solved!!!

Please post a sample workbook with some typical data. It's not easy to see from your post what you start with and what you want to end up with.

Regards

Your profile shows Excel 2003, but you uploaded a 2007 workbook.

If you're using Excel 2007+, just record a macro sorting all fields in the priority order you wish. It only takes a single sort.

Is this what you want:

Sub SortAllColumns()
Dim FirstCol As Long: FirstCol = 2
Dim LastCol As Long: LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Dim Col As Long ' Column Counter

Application.ScreenUpdating = False
For Col = FirstCol To LastCol Step 1
    Worksheets("Original").Sort.SortFields.Clear
    Worksheets("Original").Sort.SortFields.Add _
        Key:=Cells(1, Col), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal
    With Worksheets("Original").Sort
        .SetRange Columns(Col)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next 'col
Application.ScreenUpdating = True
End Sub

Regards