Sorting Help

[cmcbeath]

I am having an issue with this code i wrote to sort data based on a "Producing Month Number" column. It represents the month corresponding to production data from first production to current as an integer. I am using this to sort decreasingly by that monthly column for all the sheets in a WB, over 1000 sheets (ie. the most current production on top). The issue I am having is that not all the rows are being sorted, i believe the issue is that it is not reestablishing the LastRow variable for each sheet, even though the variable is defined within the loop. For example, if the first sheet sorted had 10 rows, it will sort that sheet correctly. Then for any sheet after that containing more than 10 rows, it will sort the first 10 decreasingly then leave the remaining rows unsorted. The attachment shows this error, the first sheet is sorted correctly, then the second sheet only has 10 rows sorted.

If anyone has any ideas on how to resolve this it would be much appreciated.

Thanks,
cmcbeath

26625

[Paul_Hossler]

You almost had it

Without the ws. parent object specifically included, it assumes the ActiveSheet



Option Explicit


Sub aSort_1()
Dim ws As Worksheet
Dim LastRow As Long

Application.ScreenUpdating = False

For Each ws In Thisworkbook.Worksheets
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("R1"), Order:=xlDescending
.SetRange ws.Range("A1:R" & LastRow)
.Header = xlYes
.Apply
End With

Next ws

Application.ScreenUpdating = True

MsgBox "Done Sorting."

End Sub

[cmcbeath]

Gah, didn't realize that.

Thank you for the help.

cmcbeath