Can this excel sorting macro be made to work on any selected range?
October 20, 2011 7:42 AM Subscribe
Inside is a recorded excel macro, designed to sort a specific range of cells on a specific worksheet from smallest to largest, from left to right, using the values in a specific range of cells in a row. Is there a way to edit this macro so that it will do the same sort on any selected group of cells on any worksheet?
posted by Lentrohamsanin to computers & internet (2 answers total)
Basically I get automatically generated workbooks of data on a regular basis. In order to compare the data from two different worksheets I sort them by a row that has consistent values across different workbooks (though the range of cells is not always the same from sheet to sheet). So below, the values in the range C6 to K22 on worksheet 15 are sorted left to right by the values in cell C6 to K6 when the macro runs. The sort row is always the top row of the range (and is always row 6 here, though that won't always be the case).
What I would like to do is make this macro usable for any selected range on any worksheet, using the values in the top row of the range. For example, on sheet 16, the range would be C6 to L15, sorted on the values in C6 to L6; on sheet 17 the range would be C6 to G25 sorted left to right on C6 to G6; and so on. Is this possible, or do I need separate macros that define the specific range to be sorted for each worksheet? It's not an unmanagable load of manual work currently, but it would be great if I could automate it.
' left_to_right Macro
' macro for sorting tables left to right on row 6
Add Key:=Range("C6:K6"), SortOn:=xlSortOnValues, Order:=xlAscending, _
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin