Solved

Error 1004 with Sort in Excel 2010 VBA

Posted on 2013-05-30
3
1,123 Views
Last Modified: 2013-05-31
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

0
Comment
Question by:cashonly
  • 2
3 Comments
 

Author Comment

by:cashonly
ID: 39208175
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
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 39209900
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
 

Author Comment

by:cashonly
ID: 39210606
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now