Sorting key

[dpaton05]

When you are sorting using range.sort method, one of the parameters is Key. If I want to sort by column A, with the header being in row 3 and the data to sort starting in row 4. What would the key be, is it A3?

 

[EXCEL MAX]

Hello Dpaton05,
Try to change sort order with variable.
This code works for me.

Option Explicit

Dim varXLSort

Sub SortRange()

    If varXLSort = xlAscending Then
        varXLSort = xlDescending
    Else
        varXLSort = xlAscending
    End If

    Range("A1:A6").Select
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=varXLSort, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").sort
        .SetRange Range("A3:A6")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

 

[dpaton05]

Thanks for the reply, I tried to adjust my code as you suggested but I still get that error on .apply.

    Dim SortOrder
        SortOrder = xlAscending
        With wsTrack
            'this copies the date column in the tblCosting
            tblrow.Range(, 1).Copy
            'this pastes it into column A of report tracking file
            .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
              'this copies the YP name column in the tblCosting
            tblrow.Range(, 4).Copy
            'this pastes it into column B of report tracking file
            .Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
               'this copies the YP name column in the tblCosting
            tblrow.Range(, 5).Copy
            'this pastes it into column A of report tracking file
            .Range("A" & Rows.Count).End(xlUp).Offset(, 2).PasteSpecial xlPasteValues

                lrTrack = wsTrack.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
                wsTrack.Sort.SortFields.Clear
                wsTrack.Sort.SortFields.Add Key:=Range("A2:I" & lrTrack), _
                    SortOn:=xlSortOnValues, Order:=SortOrder, DataOption:=xlSortNormal
                        With Workbooks(ReportTracking).Worksheets(Combo).Sort
                            'set range to sort of A3 to AO
                            .SetRange Range("A1:I" & lrTrack)
                            .header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With

 

[Michael M]

Try

wsTrack.Sort.SortFields.Add Key:=Range("A2"), _
                    SortOn:=xlSortOnValues, Order:=SortOrder, DataOption:=xlSortNormal
                        With Workbooks(ReportTracking).Worksheets(Combo).Sort
                            'set range to sort of A3 to AO
                            .SetRange Range("A3:A" & lrTrack)
                            .header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With

 

[dpaton05]

Still won't sort the sheets within the report tracking file. Not sure if I have mentioned but the header is in row 1 with the column to sort on being A.

 

[Michael M]

You seem to be setting the sort key on WsTrack.....BUT.....sorting a different work book AND worksheet !

 

[dpaton05]

I worked it out. I just needed to change the cell references around a bit. Thanks guys.

wsTrack is a different reference each iteration, the procedure that must occur is quite complex.

                lrTrack = wsTrack.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
                wsTrack.Sort.SortFields.Clear
              
                wsTrack.Sort.SortFields.Add Key:=Range("A2:A" & lrTrack), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                        With Workbooks(ReportTracking).Worksheets(Combo).Sort
                            'set range to sort of A3 to AO
                            .SetRange Range("A1:I" & lrTrack)
                            .header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With