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

On Apr 15, 11:29*am, Dave Peterson wrote:
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.





wrote:

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

--

Dave Peterson- Hide quoted text -

- Show quoted text -