Run time error when trying to apply a sort using VBA

[StuLux]

I'm getting error 1004 when I try to apply the following code to sort a range, I've tried several different ways of defining the range (which I need to do using variables) but it keeps failing on the .Apply line of code:

    Sheets(SheetName).Select    
    SheetRecordCount = Application.WorksheetFunction.CountA("D:D")
    Sheets(SheetName).Sort.SortFields.Clear
    Sheets(SheetName).Sort.SortFields.Add2 Key:=Range("A2:H" & SheetRecordCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.Sort
        .SetRange Range("A2:H" & SheetRecordCount)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Joe4]

I believe your issue is with the part in red here:

Sheets(SheetName).Sort.SortFields.Add2 Key:=Range([COLOR=#ff0000]"A2:H"[/COLOR] & SheetRecordCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers

You are supposed to tell it what column you are sorting by (not the entire range you want to sort), so it should be a single column range, i.e. if you want to sort by column D, then the part in red should look like "D2:D".

 

[Rajneesh Rawat]

I think..problem is there in Add2..it should be add

Sheets(SheetName).Sort.SortFields.Add2 Key:=Range("A2:H" & SheetRecordCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers

 

[Joe4]

I think..problem is there in Add2..it should be add

No, that part is fine. When I record a macro to do sorting, I get the "Add2" also.
The problem is with the range he used. He needs to tell it what column he wants to sort by; it needs to be a single column reference, not a multiple column reference.

 

[StuLux]

Thanks to all of you for contributing, Joe4 you are quite right it was that first reference that was wrong and, as you say I should refer to a column not the whole range.

 

[Joe4]

You are welcome.