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