Sort code setting criteria but NOT SORTING

[aereeves]

When I run the following piece of code in Excel 2007 the sort fields are being added (I say that because a manual sort after running the code contains these criteria), but the data is not actually sorted. Suggestions much appreciated. Thanks. Alicia

With ActiveWorkbook.Worksheets("Salary").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A25:A49"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("G25:G49"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range("A25:BU49")
.Orientation = xlTopToBottom
.Apply
End With

 

[Colin Legg]

Hi,

Try qualifying those range properties with the correct parent sheet.

For example:

     Dim wstSalary As Worksheet
     
     Set wstSalary = ActiveWorkbook.Worksheets("Salary")
     
     
     With wstSalary.Sort
          .SortFields.Clear
          
          .SortFields.Add Key:=wstSalary.Range("A25:A49"), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlDescending, _
                         DataOption:=xlSortNormal
          
          .SortFields.Add Key:=wstSalary.Range("G25:G49"), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlDescending, _
                         DataOption:=xlSortNormal
          
          .SetRange wstSalary.Range("A25:BU49")
          .Orientation = xlTopToBottom
          .Apply
     End With

Hope that helps...

 

[aereeves]

Thanks for your reply, Colin. Unfortunately the sort still isn't working. I modified my code as shown below. When I run the subroutine (without any breaks) nothing seems to happen (no sorting). If I set a breakpoint and step through this section of the code then I get a run time error ('1004') when I get to the .Apply line.

Any thoughts out there?

Dim wks5YP as Worksheet

Set wks5YP = ActiveWorkbook.Worksheets("Salary")
With wks5YP.Sort
.SortFields.Clear
.SortFields.Add Key:=wks5YP.Range("A25:A49"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SortFields.Add Key:=wks5YP.Range("G25:G49"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange wks5YP.Range("A25:BV49")
.Orientation = xlTopToBottom
.Apply
End With