Auto sort values from highest to lowest based off of value

I need the close% column to auto sort from highest to lowest so that I can see at a glance who the top sales person is. I have conditional formatting for the top three but I would rather them auto sort by close%, can anybody help me with this. I have attached the file, thanks for your help!

Select each block of cells one at time..

So for the first one, highlight cells A6:D17. Under the home ----> editing section, select sort and filter ----> custom sort. For sort by selection close% and then change order to largest to smallest. You will have to do that same thing for each block of cells, but that should do it.

Thank you for the response but that's not exactly what I need done. I need it to auto sort whenever the values are changed so that I don't have to do it manually each time I put in numbers.

See the attached example.
On Sheet1, the marked column of data gets sorted in ascending order every time you add another value in that range or delete or change a value.
Hope it helps.
Expand the range as you need in the Macro.
modytrane

Here's your file.
I had to create column A with copies of pct and sort all five column.
If you change any value for any sales rep.i Nashua, that block will automatically sort.
Hope it helps.
modytrane.

That is exactly what I want it to do thank you! But how do I do it for the other 3 blocks of info?

Originally Posted by qj67

That is exactly what I want it to do thank you! But how do I do it for the other 3 blocks of info?

This is exactly what I'm looking for. How can I apply this to my worksheet? I don't see how it has been done! LOL

Take a look at the attached file. This does what you wanted.
Have Fun,
modytrane

Thank you Modytrane, I really appreciate you!

Originally Posted by modytrane

Take a look at the attached file. This does what you wanted.
Have Fun,
modytrane

modytrane - how did you do it - it triyed to do the same with my excel sheet but i couldn't figure it out how did you do it. could you please explain.

cheers
Hamadah

any one can help me on that please?

Here's the Macro used in the example posted before.

Please Login or Register  to view this content.

Paste this in to your Worksheet_Change _Event.

Hamadah,
I don't know your application.
If you can't make it work, post a sample of your worksheet.

modytrane

try the attached file and let me know if this works for you.


If you change anything on any of the three sheets, it will sort the NPT % table on "Contractors" sheet.

modytrane

Originally Posted by modytrane

try the attached file and let me know if this works for you.


If you change anything on any of the three sheets, it will sort the NPT % table on "Contractors" sheet.

modytrane

modytrane
Yes it is working very fine.Thank you thank you thank you so much. you are genius. i spent more than three weeks on it but i couldn't do it. I am realy appreciated. thank you again.

best regards,
hamadah

Take a look at the attached file.
When I change values on "Data" sheet, it sorts the table on "Contractors" sheet.
I added a line in the macro that increses cell "B92" every time the sorting macro runs. It's just for testing. You can rmove that line later.

You didn't loose one green color. Look at your conditional formatting formula. You are looking for five highest values from "K73:O88". And K73 is 97.2%. so you're only coloring next four highest values in column O.

If you "Contractors" sheet is normally protected, you may have to unprotect it in the macro, before sorting and then protect it before leaving the mcaro.

If you need help with that, let me know.
modytrane.

modytrane
Sorry for late i was in vacation, Thank you so much, it is working and every thing is working perfectly. i am so much appreciated. thank you again,

is there any way to protect "contractors" sheet without effecting the autosorting, so no body can toutch it or miss with it?, because when i tried to protect it and add some value into the "Data" sheet a window showed up said that there is a debug in the system.

In the attached file, following code is used. It resides in module 1.
I've used "xyz" as the password. Change it to your password and the sheet will be unprotected before doing th sort and macro will protect it again after the sort.

Please Login or Register  to view this content.

modytrane

modytrane,

It is amazing, thank you so much for your help, i learned alot from you. The worksheet looks excellent now because of you. thank you again and i am so much appreciated.

If you are satisfied with the solution, please mark this thread "SOLVED".
modytrane

Solved ---------------------------------------

Originally Posted by modytrane

If you are satisfied with the solution, please mark this thread "SOLVED".
modytrane

modytrane
Sorry if i come back to this problem again but I did install Windows 7, Unfortunately the excel file is not working as before. i don't know why.

Please have a look in the attached file.

I checked the file and it looks like its doing the sortinng when I change one of the values in the table.
I checked it for a few different cells.
Please tell me in detail what part is not working.
Specifically tell me what values you are changing, what you are expecting and how the program is reacting differently than your expectation.
Without specific example, its hard to troubleshoot the problem.
modytrane.

modytrane,

As you know the file was working very fine before i installed Windows 7. please have a look in attached file. i explain everything in there.

modytrane, are you still following the issue with me?

Originally Posted by protonLeah

Hamadah, modytrane
Why did you resurrect a two year old thread???? You are supposed to start your own. Ancient threads, reposted, should not be answered.

Rule #2:
2. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

It was my fault, sorry i am new here and i don't know the rules... well, i will make the new thread now.

I used this code for an automatic sort, but the sort only work if I manually go into the cells that changed and hit enter. Is there anything I can add to the code to make it sort without doing this manually? The data being sorted in range B54 comes from another sheet and goes into the sorted area via formulas.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A54:E63").Select
Selection.Sort Key1:=Range("B54"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Calculation = xlCalculationAutomatic

End Sub

Thanks for any help!

I used this code for an automatic sort, but the sort only work if I manually go into the cells that changed and hit enter. Is there anything I can add to the code to make it sort without doing this manually? The data being sorted in range B54 comes from another sheet and goes into the sorted area via formulas.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A54:E63").Select
Selection.Sort Key1:=Range("B54"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Calculation = xlCalculationAutomatic

End Sub

Thanks for any help!

Hi
I copied your macro and pasted into my sheet but i could not get it to work