Excel sort macro

[sbitaxi]

Hello:

I have been creating a reporting tool, and would like to be able to
sort from a macro using criteria pulled from a cell.

How do I tell Excel to select the Sort Key based on data in another
cell/worksheet. Right now, the script tells it to sort the range by
column B, starting in row 3, ascending order. I'd like to have a drop
down list of the "Fields" aka columns, in another worksheet. The user
selects the field, clicks a button and Excel generates a report. This
will be part of another macro that filters out records and copies them
into a report template.


Range("A1:AC23").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Hopefully this is clear.



Steven

 

[Per Jessen]

Hi

Assign the field selected to a variable, then use the variable as Sort Key.

sKey=Range("A1").Value ' Change to point at the cell with your "drop down
list"
Range("A1:AC23").Sort Key1:=sKey,...

Regards,
Per

 

[sbitaxi]

Thank you Per, it is a lot closer, but I'm getting a 1004 Error "The
text you entered

 

[sbitaxi]

Thank you Per, it is a lot closer, but I'm getting a runtime error
'1004' "The text you entered is not a vaild reference or defined
name."

Checked the sKey, and that is definitely drawing the column headers
from the reference. My code isn't great, by any means, but up until
the sort, it works. Any thoughts?

Sub Monthly()
' identifies sort field
sKey = Sheets("Reports").Range("C15").Value

' clear old report data; label report
Sheets("ReportOutput").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = Sheets("Reports").Range("MonthList")
Sheets("ReportOutput").Range("A3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents

' filters records on Master spreadsheet, based on criteria in
MonthList (named cell)
If Sheets("Reports").Range("MonthlyActivities") = "Yes" Then

' If there are two levels of criteria
Sheets("Master").Select
Selection.AutoFilter Field:=21,
Criteria1:=Sheets("Reports").Range("MonthList"), Operator:=xlOr _
, Criteria2:="=*monthly*"
Else
' one level of criteria
Sheets("Master").Select
Selection.AutoFilter Field:=21,
Criteria1:=Sheets("Reports").Range("MonthList")
End If

' copy filter results, paste into ReportOutput template
Sheets("Master").Range("2:1000").Copy
Sheets("ReportOutput").Range("A65536").End(xlUp).Offset(1).PasteSpecial
Sheets("Master").Select
Selection.AutoFilter Field:=21
Range("A2").Select

' Sort ReportOutput by field defined in sKey
Sheets("ReportOutput").Select
Range("A1:AC23").Sort Key1:=sKey, Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A3").Select

End Sub

 

[Dave Peterson]

What's in that key cell? Is it a value from the headers?

Dim res as variant
dim RngToSort as range
dim KeyRng as range

set keyrng = worksheets("sheetname with the dropdown").range("a1")

with worksheets("sheet with the table to be sorted")
'headers for the data in row 2
set rngtosort = .range("a2:ac23")
end with

if keyrng.value = "" then
msgbox "Please put choose a column to sort by"
exit sub
end if

res = application.match(keyrng.value, rngtosort.rows(1), 0)

if iserror(res) then
msgbox "design error--no match in the headers! Contact Steve right away!"
exit sub
end if

with rngtosort
.cells.sort key1:=.columns(res), Order1:=xlAscending, _
Header:=xlyes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with

=====
Untested, uncompiled. Watch for typos.

 

[sbitaxi]

Hi Dave,

It is indeed a value from the headers. I just realized, look at your
code, that I was telling it to sort from row 1 rather than row 2 where
the field headers are. I changed that and it works!!

Thank you! I'm going to drop in your code and see how it affects the
macro. Oh the frustrations of a simple typo.


Steve