If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
numeric sort on one sheet, automatically sorts alphabetical on another?
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 |
#3
|
|||
|
|||
numeric sort on one sheet, automatically sorts alphabetical on another?
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
DELETE DUPLICATE DATA IN A SHEET AUTOMATICALLY | Sony Manoj | General Discussion | 1 | November 20th, 2005 08:39 AM |
alphabetical sort while ignoring preceeding numbers/symbols | silver fox | General Discussion | 1 | October 2nd, 2005 01:54 AM |
Formula to sort names in alphabetical order? | Fredrik Jonsson | General Discussion | 0 | September 1st, 2005 04:33 PM |
help moving info from one sheet to another automatically | Grace | Worksheet Functions | 3 | April 29th, 2004 04:29 PM |
Is it possible to automatically sort cells easily? (I'm going mad!) | Andy Sandford | Worksheet Functions | 2 | November 9th, 2003 03:09 PM |