named range sort

[Legacy 15162]

I am utilizing access to dump data in an excel spreadsheet. after the data is dumped, i would like to sort the data by a specific column in excel. I already have a named range called sort that was defined in excel. how can i get this code to work properly?

If xlWS.Name = "Outerlier Report" Then
xlWS.Range("B5") = TxtBeginPeriod & " THRU " & TxtEndPeriod
xlWS.Cells.Name("SORT").Activate
xlWS.Cells.Sort Key1:=Range("V12"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If

 

[Andrew Poulsom]

Maybe:

With xlWS.Range("SORT")
   .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

You may need to change .Cells(1,1) if you don't want to sort by the first column.

 

[Legacy 15162]

If xlWS.Name = "Outerlier Report" Then
xlWS.Range("B5") = TxtBeginPeriod & " THRU " & TxtEndPeriod
With xlWS.Range("SORT")
.Sort Key1:=Range("V12"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End If


I guess the WITH statement is the key........When would you normally utilize the with statement?

 

[Andrew Poulsom]

I only used With because I wanted to reference that range in the SortKey1 argument. If you know V12 is within the range then this will do it:

xlWS.Range("SORT").Sort Key1:=Range("V12"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom