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?

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

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" wrote
in message ...

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