Looping through worksheets to sort

[cmcbeath]

I am trying to loop through all sheets in a WB and sort based on Column R decreasing. I have tried multiple different methods from various forums and I continue to get runtime error on my ".Apply". I have attached a sample workbook with a few sheets and a little code, the number of rows changes so i need the range to be dynamic.

Thanks for the help

[Paul_Hossler]

Try this

BTW your description said "decreasing" but your macro said "xlAscending"

Option Explicit


Sub SortSheetsColR()
    Dim ws As Worksheet
    Dim r As Range, r1 As Range


    Application.ScreenUpdating = False


    For Each ws In Sheets
        With ws
        
            Set r = .Cells(1, 1).CurrentRegion
            Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
        
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=r1.Columns(18), Order:=xlAscending
                .SetRange r
                .Header = xlYes
                .Apply
            End With
        End With
        
    Next ws
        
    Application.ScreenUpdating = True
        
End Sub

[cmcbeath]

Sheesh, that would do it. Thank you for the help..

Callum