creating sorted copy of table on second sheet

[Wilfried Hennings]

Hello all,
table on sheet 1 consists of (say) a row with names and a row with room
numbers and is sorted by name. (First row is header and not included in
sorting.)
I want to create a copy of this table on a second sheet containing the
same data sorted by room number which automatically re-sorts when the
data in the first table is changed.
I could think of a matrix function but could not find any.
Any idea?

--
email me: change "nospam" to "w.hennings"
Dipl.-Ing.(=M.Sc.Eng.) Wilfried Hennings c./o.
Forschungszentrum (Research Center) Juelich GmbH, MUT
<http://www.fz-juelich.de/mut/index.php?index=3>
All opinions mentioned are strictly my own, not my employer's.

 

[Don Guillett]

How about just sorting by any column desired in the original table that is
named sortrange

Sub Sort()
mycol = ActiveCell.Column
End If
[sortrange].Sort Key1:=Cells(1, mycol), Order1:=xlAscending,
Orientation:=xlTopToBottom
End Sub

 

[Dave Peterson]

A different--nonsorting approach.

Select your range (include the last row of headers) and do
Data|Filter|Autofilter.

Then instead of sorting, you can just filter on your favorite field. Use the
dropdown arrow and you can show just the values you want to see.

It might be sufficient.

 

[Dave Peterson]

I find it easier to keep my input in one spot and produce different sorts when I
need them. I think that it's too difficult to get things like you want to work
100% of the time (my opinion only).

I'd either have a macro that would sort the data in place, use autofilter, or
generate a new worksheet (sorted in the correct order) whenever I needed it.