You're welcome, it would be nice if this could be done without a macro but I can't find anything that suggests you can.

A quick google shows that Excel "clears the undo stack" after running a macro and seems there is no way to change that. Unfortunate side effect (I assume this is a performance thing).

I better not make a mistake when entering data

Thanks again Ian.

The only other way to do it would actually to not use the code on change but add a button with the sort code and manually do it when you want an update - but this largely defeats the purpose of what you seem to be trying to achieve (ie might as well just set a filter on it and sort as and when needed rather than use a macro).

And another possible trick that might help with undo.

If you are doing a lot of changes you could add a bit of code that allows you to put a value in a particular cell that if it is set stops the sort code from running (and then turn it back on just before you change the last entry so the last change triggers it.

The new test is:
If Range("E18").Value <> "Y" Then

If I put a Y in the cell E18 on the 2014 sheet then the sort won't run and undos will stay available. Removing the Y from the cell (or setting it to anything else) will mean sort will run when the source table changes. If you want to put it somewhere else on the sheet then change E18 to whatever cell you put the Y in (or if you want to use something other than Y then change the Y to whatever you would rather use.

Text

1 23 45 67 89 1011 1213 1415 1617 1819 2021 2223 2425 2627 2829 30

Private Sub Worksheet_Change(ByVal Target As Range)     Dim KeyCells As Range        ' The variable KeyCells contains the cells that will    ' cause an alert when they are changed.     If Range("E18").Value <> "Y" Then        Set KeyCells = Range("A1:X15")    CurrCell = ActiveCell.Address         If Not Application.Intersect(KeyCells, Range(Target.Address)) _     Is Nothing Then    ' Sort cells.         Range("A36:B50").Select        ActiveWorkbook.Worksheets("2014").Sort.SortFields.Clear         ActiveWorkbook.Worksheets("2014").Sort.SortFields.Add Key:=Range("B36:B50"), _            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal         With ActiveWorkbook.Worksheets("2014").Sort            .SetRange Range("A36:B50")             .Header = xlGuess            .MatchCase = False             .Orientation = xlTopToBottom            .SortMethod = xlPinYin             .Apply        End With         Range(CurrCell).Select    End If     End If    End Sub

If you don't want to use macros, there's a very easy way to achieve what you're after, if you're prepared to be flexible with where the data gets entered.

http://www.camieabz.co.uk/files/sorting.xlsx

Basically, you would shift the data entry area elsewhere, then in the area where you want the 'sorted data' to be displayed do the following (my spreadsheet's references used):

=LARGE($B$250:$B$258,1)
=LARGE($B$250:$B$258,2)

and so on until all your numerical values are listed where you want them.

Then in the 'names' column, you want

=INDEX($A$250:$B$258,MATCH($B1,$B$250:$B$258,0),1)

This throws back the name next to each value. There might be an issue with duplicate values, so that's likely in this solution, it's probably not going to be workable.

Forgot to add.

I would use the macro if macros were an acceptable route (if it's to be shared with others, sometimes macros are not the best option).

What's more, I tend to allocate an entire sheet for all data entry and another for working, and then pull data from it for the reading sheets. There are several advantage to this:

1. It separates the data entry (the area where mistakes will be made) from the calculation and working data. The latter might be locked or even hidden, to prevent accidental or malicious fiddling.

2. It makes it easier for users to split in one direction or another when editing it in future.

3. It reduces the time spent error checking. No need to double check the working page, if all the data entry is elsewhere.

4. Having 'reading sheets' makes for printing, chart exporting and so on, and if you want to copy and paste a read sheet because it would be good for another idea, you don't copy all the formulas, data entry and the rest.

I don't use macros too often, but if I do, I separate them into several steps in my head. I might want to have a spreadsheet go to named range, in a named sheet, look for the first entry, and if it's a '1', do further steps, but if it's anything but a '1' stop.

So then you break into "how do I goto named sheet?", "how do I goto named range?", and so on. Sometimes it's trial and error and I have seen 20 mini-macros, all doing one step each, then I pull the code from each, and make them into one.

There is masses of help online for pretty much every macro idea you might have, so you don't have to be a VB expert. All you need is to know what you want to do, and how to get it across to the VB folk in such forums, and stress that you don't want to be bamboozled with VB stuff.

Thanks Cammy, I think I will stick with macro, it's a personal ss so will be fine.
As I don't enter that much info at a time, going wrong isn't that big of deal, I can either correct it or close SS and start again.

I don't do many changes, so losing the "undo" isn't a great loss.

I'm a happy bunny with what you sorted for me.

Trying to add another marco.

I've altered the original macro to cells A30:B40 and works.

I'm now trying to add another macro for cells A43:B53

I've tried copying macro, but think I need to change the name of second marco but can't seem to do it?



Any ideas? (anyone)

Edited by deleted (Mon 05-Jan-15 10:15:37)

Found out that I couldn't have two separate macros , had to combine them into one.
Now working as I want it too.