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