?
Solved

Error 1004 with Sort in Excel 2010 VBA

Posted on 2013-05-30
3
Medium Priority
?
1,212 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

800 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