Error 1004 with Sort in Excel 2010 VBA

I'm trying to do a 2 key sort in Excel 2010 and am having a problem.

I went to the worksheet, turned on macro record, selected the data I wanted to sort, clicked on the sort button, set my parameters and clicked OK.  It sorted fine. Then I stopped the macro recording.

Then, I went to the macros button, selected the macro I just recorded and clicked run.  The code worked fine up to the .Apply statement and then generated a 1004 error.  WHY???

This is the code the macro recording generated:
Sub fixsort2()
'
' fixsort2 Macro
'

'
    ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort.SortFields.Add Key _
        :=Range("E2:E6025"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort.SortFields.Add Key _
        :=Range("GR2:GR6025"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("PistonTest").QueryTables(1).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

cashonlyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Robberbaron (robr)Connect With a Mentor Commented:
the xl2010 version is sorting .QueryTables(1).

it is using that as the source of the table.  why it throws that error I dont know without testing on workbook.
0
 
cashonlyAuthor Commented:
More info.  I went oldschool and wrote the macro like in Excel 2000 and ran it in Excel 2010 with no problem:

Sub fixsortoldschool()
   ThisWorkbook.Worksheets("PistonTest").Range("A1:GU6025").Select
    Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("GR1") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

End Sub

Open in new window


But I'd still like to know why it doesn't work with the Excel 2010 method of sorting.
0
 
cashonlyAuthor Commented:
robberbaron,

That's interesting.

I just created a new WB and populated a 50 row x 5 column range with random numbers, made them static and added a header.  Then I did the same steps in recording a macro of me sorting them on 2 different columns, one ASC and the other DESC.

This time it DID NOT use Query Tables.  I use Query Tables in parts of my code and didn't even catch it that the macro was using those for a sort which I DID NOT want.

Your keen observation skills have answered my question.  Thank you!
0
All Courses

From novice to tech pro — start learning today.