Solved

Error 1004 with Sort in Excel 2010 VBA

Posted on 2013-05-30
3
1,190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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