Why separate worksheets when using excel?

Can this excel sorting macro be made to work on any selected range?

• 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? 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. Macro follows: Sub left_to_right() ' ' left_to_right Macro ' macro for sorting tables left to right on row 6 ' ' Range("C6:K22").Select ActiveWorkbook.Worksheets("15").Sort.SortFields. _ Clear ActiveWorkbook.Worksheets("15").Sort.SortFields. _ Add Key:=Range("C6:K6"), SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("15").Sort .SetRange Range("C6:K22") .Header = xlYes .MatchCase = False .Orientation = xlLeftToRight .SortMethod = xlPinYin .Apply End With End Sub

• Answer:

  You can replace this: ActiveWorkbook.Worksheets("15") with just ActiveSheet to get the sheet currently on top. And your currently selected range is referenced by Selection. Within a Selection, you have Rows and Columns. So you can take out the line Range("C6:K22").Select and just replace .SetRange Range("C6:K22") with Selection, I believe. I'm not quite 100% sure what your code does, but if you want to send me a sample workbook, I'd be glad to test a solution for you.