Auto Sort By Purchase Order Number

[Russ At Index]

Hi All,

I would like to perform the folllowing within a worksheet.

In column "A" i am entering a PO number then i have assorted
information relating to that PO in columns B.C.etc.

My question is .... would it possible to , when a PO has been entered
into col A can Excel automatically sort that row to run in numerical
order ?

Thanks In Advance

Russ

 

[HalfAce]

Hello Russ,
Sure.
Try one of these in the sheet module.
(Right click the sheet tab & choose View code.)
Paste one or the other routine (not both) into the white sheet module.
If you want to sort the entire column (from row 1 down):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then _
  Columns("A:D").Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub

If you don't want to include row 1 (headers for instance) then you can use this one instead.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LstRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then _
  Range("A2:D" & LstRw).Sort Key1:=Range("A2"), Order1:=xlAscending
End Sub

Note: In either case, amend the column D in the code to the furthest column out that you want included in the sorting.

Press Alt+Q to get back to the sheet & try it out.

 

[Russ At Index]

Morning Half Ace,

Works like a Saturday morning dream ..............

Unfortunatley i am at work .........

Thanks Again

Russ