Sorting

nickharr · Jun 24, 2011

Is it possible to have a column in a worksheet permanently sorting in alphabetical order? The sheet I have in question is getting information from another worksheet when certain criteria occur.
I can obviously do a sort each time I need to look at this sheet but it would be really useful if there was a way to make it permanent.
To explain a little further – I have two worksheets – One “Orders” and the other “Back Orders”. In the Orders sheet I would have say 100 lines and 20 of these would not be delivered and therefore marked as Back Orders. The Back Orders sheet would now show details of the 20 entries. Column A of this Back Orders sheet would be the Product Name and this is the one I need to sort alphabetically. I might then have to make a further Order of another 50 lines and 10 of these will be marked as Back Orders. These will then be showing on the Back Orders sheet which now has 30 entries on it but these will be added to the bottom of the sheet rather than alphabetically.
Hope I have explained this clearly! Any ideas would be really helpful.

VoG · Jun 24, 2011

Try like this: right click the sheet tab, select View Code and paste in

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.UsedRange.Sort Key1:=Range("A2"), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.EnableEvents = True
End Sub

nickharr · Jun 24, 2011

Peter - thanks for your reply. As you have already guessed - I am a complete novice with codes etc but I have tried copying and pasting but it doesn't appear to work. I tried copying the sheet and pasting with just "values" but I can't seem to get it to work either. Should it be looking at the column A and getting that sorted in alphabetical order?

VoG · Jun 24, 2011

Yes, it sorts based on column A. The code has to go in the code module of the sheet that you are pasting to.

Tested and working here.

nickharr · Jun 24, 2011

Sorry Peter - I'm probably doing something wrong here but I just did a new sheet with a very simple layout. In A1 I entered CCC, in A2 BBB and A3 AAA and then pasted your code into the "View Codes" on the tab. Should I now do anything else or would you expect to see A1 showing AAA, A2 showing BBB and A3 showing CCC?

VoG · Jun 24, 2011

You need to add the code before entering data. It is the entering of data that causes the code to run.

I've uploaded my test workbook here http://www.box.net/shared/yexk6v0emlsdfmshgr7x

nickharr · Jun 24, 2011

Hi Peter - I opened a new workbook, selected a worksheet and right clicked on the tab to select View Codes and then pasted to copy of the code that you sent. I then entered CCC in A1 and BBB in A2 - I was then hoping that it would swap these two around but it didn't!
I do apologise if I am being stupid and for taking so much of your time but now I know it might be possible to do, I really want to get it working!
Do you think some of my settings are not set to receive codes?

VoG · Jun 24, 2011

The code assumes that row 1 contains headings so your example wouldn't sort. If you then entered AAA in A3 then it would.

Macros must be enabled (lowest security setting).

nickharr · Jun 24, 2011

Still no luck! If I added another figure into your example in A17 - say 5 - would you expect this to move up to A11? It certainly doesn't on my computer!

VoG · Jun 24, 2011

Try closing Excel. Then download the example that I uploaded and check if that works.

Sorting

nickharr

Active Member

Excel Facts

VoG

Legend

nickharr

Active Member

VoG

Legend

nickharr

Active Member

VoG

Legend

nickharr

Active Member

VoG

Legend

nickharr

Active Member

VoG

Legend

Similar threads

Forum statistics

Share this page

Sorting

Active Member

Excel Facts

Legend

Active Member

Legend

Active Member

Legend

Active Member

Legend

Active Member

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock