Repeating a sort in Excel

Take a look at the last post at this link.

Thanks, I did find that previously but it didn't work for me.

I sort a lot of columns (trips in bus timetables).

I sorted one sheet (by column) using my desired preferences.

Then I sorted that sheet (by column) using the A to Z button (which produces an irrelevant result because everything goes to the top of the page) then I hit CTRL+Z.

My original sort preferences are lost and the dialogue box has reverted to sorting by rows with no preferences entered.

So that didn't help, unless I am missing something in following those instructions??

Could you record a macro while you sort and then use the macro for future sorts?

Thankyou.

Yes I suppose I could if I had a lot of sorts to do using the same preferences (in terms of which rows I sort by first, which one second etc).

However in other cases where I just sort by, for example, rows 5, 12 and 15 a few times, then another day on a different sheet do a few sorts by rows 7, 14 and 17 etc I'm guessing I would need to record separate macros each time.

It sure was a lot easier when you could just hit the F4 button!

Also I am sure it used to be that Excel would actually remember the last sort you did on a particular worksheet so you could come back some days later and use it again.

You could write a macro which looked for all the rows you previously select and have it use those rows to sort with.

I was just starting to experiment with a macro like the one I mentioned in my previous post and it makes me ask if you meant "columns" instead "rows" when you said

However in other cases where I just sort by, for example, rows 5, 12 and 15 a few times, then another day on a different sheet do a few sorts by rows 7, 14 and 17 etc I'm guessing I would need to record separate macros each time.

If you did mean rows I need more explanation of what you're doing.

I do both.   Sometimes I sort by rows sometimes I sort by columns.   Above I was referring to sorting by rows.

As an example of sorting by columns sometimes I compile a list of bus departures on several bus routes.   I copy the times and route numbers of each route into rows on a sheet and sort firstly by the column containing the departure time and secondly by the column containing the route number.

So I end up with a list of bus times in time order but if two buses depart at the same time then the Route 1 bus will appear before the Route 2 bus.

(I just recorded a macro to do that sort but it became specific to the worksheet I recorded it on.  I need to learn more about how I can simply repeat the keystrokes on any worksheet that I select data on).

As an example of sorting by rows I have a bus timetable in which each column shows the time that each bus departs each stop and at the top of the column it shows which driver does each trip.

When I sort by rows I end up with the data ordered so that it shows me the trips run by each driver in sequence for that driver, i.e. all the trips by Driver 1 first, then all the trips by Driver 2 and so on.

Hope that helps!   :-)   Thank you for your efforts.

If the macro is in a Module it should be available to all the sheets in a workbook.

I'm still confused about sorting by rows. In my (2003) version of Excel I can only sort on a single, contiguous, group of rows. You seem to be saying that you can for example sort on rows 7, 14 and 17. Is that correct?

Sorry it may be the way I am explaining it.

Yes I sort on a single, contiguous, group of rows.

I just mean when I set the Option to "sort left to right" I set the Sort options to
"Sort by" row 7
"then by" row 14
"then by" row 17

Thanks again.

(BTW it is 2am here so I will check any further replies tomorrow).

Sorry it's been a while.  :-)

I'm back to my dilemma of how to sort by rows on a repetitive basis on many sheets within a file.

I have a lot of sheets where I have to sort by rows based on the values in column D.

I tried recording a macro but when I replayed it on the next sheet it went back to the sheet I recorded it on.

I just don't understand how to get the macro to replay on selected rows of the active sheet rather than going back to the sheet on which the macro was recorded.

I'd welcome any thoughts, and ..... btw ....... Merry Christmas too!

Many thanks
Regards
Glen

A bit more info.   Thanks!

The error I get is:

Run time error '9':
Subscript out of range

------------------------------------

Here is my macro.   My problem seems to be is that it refers to the sheet I recorded it on ("Glenfield DOWN") in the selected range there, but I want it to refer to any sheet with any range I choose.


Sub Sort_Inspectors_by_D()
'
' Sort_Inspectors_by_D Macro
' Sort_Inspectors_by_D
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    ActiveWorkbook.Worksheets("Glenfield DOWN").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Glenfield DOWN").Sort.SortFields.Add Key:=Range( _
        "D4:D329"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Glenfield DOWN").Sort.SortFields.Add Key:=Range( _
        "B4:B329"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Glenfield DOWN").Sort
        .SetRange Range("A4:I329")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

You could record a macro that by way of an InputBox or two you could input the parameters that vary.

Thanks  :-)    but I'm not very experienced with macros.

Is there a way I can record a macro that, for example, sorts by the same column (D) in my case, but will sort on any group of rows I select, of any number of rows, in any file or sheet?

I thought recording the macro with the 'Use Relative References' button on may help, but when I tried it on a blank sheet it only worked on that same sheet.

Substitute ActiveSheet (or the name of some target sheet) for the name of the sheet in the macro you recorded.

It would help if you posted the macro and described how you wanted it changed.

Hi, thanks for your advice and sorry for the delay during the holiday period.

I just recorded the macro afresh and it is below:

This sorts the rows by the values in column D but when I try to run it on another sheet it refers back to the sheet I recorded it on ("Cabramatta").

Sub Rail_Inspector_Sort_by_Column_D()
'
' Rail_Inspector_Sort_by_Column_D Macro
' Rail_Inspector_Sort_by_Column_D
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    ActiveWorkbook.Worksheets("Cabramatta").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Cabramatta").Sort.SortFields.Add Key:=Range( _
        "D18:D49"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Cabramatta").Sort
        .SetRange Range("A18:H49")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


I then took your suggestion and substituted ActiveSheet for the name of the sheet (revised macro is below).

That improves things, thank you, except that the macro is still sorting only for the specific rows I originally selected when I recorded it.

I really want the macro to sort on any group of rows that I select, which can be any random number of rows in any sheet in any workbook.

How do I take out the specific rows references?   Sorry I'm unfamiliar with the syntax required.


Sub Rail_Inspector_Sort_by_Column_D()
'
' Rail_Inspector_Sort_by_Column_D Macro
' Rail_Inspector_Sort_by_Column_D
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "D18:D49"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A18:H49")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub




Thank you for your help.
Regards
Glen

Do something like this.

Dim startcell as string
Dim endcell as string

startcell = inputbox("Enter start cell")
Endcell = inputbox("Enter end cell")


Then instead of
SetRange Range("A18:H49")

You can do

SetRange Range(startcell & ":" & endcell)

Thank you for that.

Sorry to sound so dumb :-) but do I put these lines .............

Dim startcell as string
Dim endcell as string

startcell = inputbox("Enter start cell")
Endcell = inputbox("Enter end cell")

................. at the beginning of my macro ahead of where I currently have:

   ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

The change to 'SetRange' I can figure out.

I did try to get the sequence right myself but it kept failing, although I did get the input boxes to work.

It's a shame though that I can't simply define the rows to be sorted by selecting any random group of rows like I used to do when the F4 key worked as a 'Repeat Sort'.

Thanks again and sorry this is taking so long for you.

Regards
Glen.

You can put the Dim-s anywhere before try to use them but it's pretty standard to put them at the top. The InputBox lines can be put anyplace after the Dim's but before you need to use startcell and endcell.

If you have any problems please show the whole macro.

Excellent, that is exactly what I needed.

Thank you so much.   :-)

I actually needed to sort by Column D and sort ascending but I was able to make those changes myself.

I even managed to set a second sort criteria by repeating the .SortFields line with a different column selection.

I really appreciate your help.

Regards
Glen

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012