PDA

View Full Version : Solved: Excel Sorting Help



Kindly_Kaela
02-06-2007, 03:09 PM
I'm trying to sort Excel data in a different sheet that's displayed. 'DISPLAY' is the sheet I want to view while the macro is running. 'DataBase' is the sheet that sorting should happen. Here is the macro I recorded....


Sheets("DataBase").Select
Range("J3:S100").Select
Selection.Sort Key1:=Range("N3"), Order1:=xlAscending, Key2:=Range("M3") _
, Order2:=xlAscending, Key3:=Range("R3"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("DISPLAY").Select


If I use this macro, the program quickly switches over to 'DataBase' and then back to 'DISPLAY'. So logically, I figured this code would work instead...


Sheets("DataBase").Range("J3:S100").Sort Key1:=Range("N3"), Order1:=xlAscending, Key2:=Range("M3") _
, Order2:=xlAscending, Key3:=Range("R3"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal


However, it doesn't. I'm getting "RUN-TIME ERROR 1004. The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort by isn't the same, or blank"

Why would one work, but not the other? Please help me figure out the proper code so I don't see 'DataBase' sheet flicker in and out.

Thanks!
Kaela
:cloud9:

mdmackillop
02-06-2007, 03:28 PM
Hi Kaela,
You need to tie your Key ranges to the sheet
Sub Sorts()
With Sheets("DataBase")
.Range("J3:S100").Sort Key1:=.Range("N3"), Order1:=xlAscending, Key2:=.Range("M3") _
, Order2:=xlAscending, Key3:=.Range("R3"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

Kindly_Kaela
02-06-2007, 03:40 PM
Nicely done, thank you MD!!

:beerchug: