AutoSort in VBA

[Guest]

Is there a simple way to Autosort a specific area on a work sheet.

E.G.

Column A Column B Column C
1) Salesman Items Sold Total $ Sales
2) Joe 3 $5000.00
3) Tony 2 $2500.00
4) Sal 1 $1400.00

These numbers are actually imported from different worksheets and databases,
so I'd like to sort from the existing sheet from High to Low based on top $
sales.

Can anyone shed some light please.

 

[Guest]

The following piece of code will do what you want:

Range("A2:C4").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This is assuming that your data start on row 2 and is only 3 lines long, you
will obviously have to change the 'Range' reference to whatever suits your
dataset.
Also note that is actually only TWO lines of code, the lines starting
Selection, OrderCustom, and Dataoption are only 1 line, if you're typing it
in remove the underscore charactersto create one line, if you're cutting and
pasting, it will work fine as it is.

I'm not sure about making the sort happen automatically, I would be inclined
to add a command button to the sheet and link it to the above code so that
the user will obtain sorted data whenver the button is pressed.

HTH

Neil
www.nwarwick.co.uk