numeric sort on one sheet, automatically sorts alphabetical on another?

How can I set up a sheet where I would input a list of names and numbers
sorted numerically, and have that data automatically placed in alphabetical
order on another sheet.

The application is a simple key locker i.e.
Sheet 1
num desc
001 front door
002 back door
003 firehouse
004 apple house

sheet 2 would automatically display as
desc num
apple house 004
back door 002
firehouse 003
front door 001

the printed output is most important so I could just sort before printing.
But if the automation is possible it would be very interesting to see how
that could work.
Any ideas?

Well now, isn't that cool!
I had a bit of trouble putting it into the sheet itself, the option to put
it there wasn't instinctive so I kept dropping it into a module. But I got
it in the right place now and it works as advertised.

Thanks a whole bunch.


"Leith Ross" <[email protected]> wrote
in message news:[email protected]...
>
> Hello,
>
> Here is the automated version. Place this code inside the destination
> worksheets' Worksheet_Activate() event. Change the variables SrcCell
> (Starting cell on the source sheet), SourceSheet (to the name of the
> source data worksheet) and DstCell (the starting cell of where the data
> will be copied) to what match your layout. The macro automatically sizes
> the source range, so you can add to it without changing addresses in the
> macro code.
>
>
> Code:
> --------------------
>
> Private Sub Worksheet_Activate()
>
> Dim A, B
> Dim DstCell As String
> Dim DstCol As Long
> Dim DstRng As Range
> Dim I As Long
> Dim FirstRow As Long
> Dim LastRow
> Dim SourceSheet As String
> Dim SrcCell As String
> Dim SrcCol As Long
> Dim SrcRng As Range
>
> 'Variables for source and destination
> SrcCell = "L10"
> SourceSheet = "Sheet1"
> DstCell = "D10"
>
> 'Find all data entries on the source worksheet
> With Worksheets(SourceSheet)
> SrcCol = .Range(SrcCell).Column
> FirstRow = .Range(SrcCell).Row
> LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
> Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 1))
> End With
>
> With ActiveSheet
> 'Copy the data from the source sheet to the destination
> DstCol = .Range(DstCell).Column
> LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
> Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 1))
> DstRng() = SrcRng()
> 'Reverse the data
> For I = 1 To DstRng.Cells.Count
> A = DstRng.Cells(I, 1).Value
> B = DstRng.Cells(I, 2).Value
> DstRng.Cells(I, 1).Value = B
> DstRng.Cells(I, 2).Value = A
> Next I
> 'Sort the data from A to Z
> DstRng.Sort Key1:=.Range(.Cells(1, DstCol), .Cells(LastRow, DstCol + 1))
> End With
>
> End Sub
>
> --------------------
>
>
> --
> Leith Ross
>
>
> ------------------------------------------------------------------------
> Leith Ross's Profile:
> http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=497030
>