Simple looping SORT, column by column

[LORDMARKS]

Hi All.

Just after a little help to get this loop to work. I need to sort 100 columns individually so I can export the data to a report. I have tried adapting the sort code to run on a loop 1-100, but I am missing something as I cant get it to run.

If anyone has any idea what I am doing wrong, or if you have a better solution, that would be great.

Sub ReportGen()
'Application.ScreenUpdating = False
Dim Col As Integer
Col = 1 'test variable
'For Col = 1 To 100
With Sheets("REPORT")
    .Unprotect Password:=("****")
    .Range(Cells(1, 1), Cells(160, 100)).Value = Sheets("CALCS").Range("DB401:GW562").Value
    '.Range(Cells(1, Col), Cells(100, Col)).Sort Key1:=.Range(Cells(1, Col)), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    .Range("A1:A160").Sort Key1:=.Range("A1"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    .Range("B1:B160").Sort Key1:=.Range("B1"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    .Range("C1:C160").Sort Key1:=.Range("C1"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal '...........ETC.........
End With
'Next Col
End Sub

I have linked out the line I am trying to get to work, and written the examples below to show what I am tring to do.


Thanks as always

 

[CharlesChuckieCharles]

This should do it

Sub ReportGen()
'Application.ScreenUpdating = False
Dim Col As Integer
Dim myDepth As Long
With Sheets("REPORT")
    .Unprotect Password:=("****")
    .Range(Cells(1, 1), Cells(160, 100)).Value = Sheets("CALCS").Range("DB401:GW562").Value
    
    For Col = 1 To 100
        myDepth = 160
        .Range(.Cells(1, Col), .Cells(myDepth, Col)).Sort Key1:=.Range(.Cells(1, Col)), Order1:=xlDescending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        
    Next Col
    
'    .Range("A1:A160").Sort Key1:=.Range("A1"), Order1:=xlDescending, Header:= _
'        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
'        DataOption1:=xlSortNormal
'    .Range("B1:B160").Sort Key1:=.Range("B1"), Order1:=xlDescending, Header:= _
'        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
'        DataOption1:=xlSortNormal
'    .Range("C1:C160").Sort Key1:=.Range("C1"), Order1:=xlDescending, Header:= _
'        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
'        DataOption1:=xlSortNormal '...........ETC.........
End With
End Sub