auto sort

[Vicki]

I currently am working on a excel spreadsheet that needs to be a working
database. It is currently linked to bloomberg, (stock prices are
automatically fed and updated) (live feed) The only column that will change
everyday is the last one, although when the last one changes the positions of
the stocks need to be sorted based on that last column in ascending order.
can anyone assist me with this. I am on a deadline and have been working on
this project for the last week, not which direction i should be going with
this. (vlookup, macro) not sure. anyone's help would be greatly appreciated.

 

[ryguy7272]

Right-click on the WS where you want to do your sort and paste this code into
the window that pops up:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column <> 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Be mindful of the fact that the current sort column is defined as:
..Sort Key1:=Range("B2")
Change to suite your needs.

Regards,
Ryan---

 

[Vicki]

Thank you Ryan,

I am typing this in now the column that will determine the sort of the
entire row is J and the columns start at 6 and go up. When I started typing
OrderCustom:=1,MatchCase:=False
Orentation:=xlTopToBottom
I receive "compile error Expected: Expression"

Thanks so much for your help. So what are the next steps after put the code?

 

[Vicki]

Hi Ryan,

So I cut and past the code and then I updated colum J's numbers and it didnt
automatically sort. I'm sorry thanks for you patience

 

[ryguy7272]

Try the code below...remember, right-click on the tab of the worksheet that
you want the code to run in (this is a ‘Private’ subroutine, so it will only
affect the sheet that you paste the code into, as opposed to affecting all
the sheets in the entire workbook).
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column <> 10 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 3))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Two lines of code are important here:
Line #1:
If Target.Column <> 10 Then Exit Sub
This tells Excel to do the sort after you finish with entering data into
ColumnJ (i.e., when you reach ColumnK)

Line #2:
..Sort Key1:=Range("B2"),……..etc.
This tells Excel to sort according to the data in ColumnB.


If your data doesn’t extend to ColumnJ, or extends past columnJ, change
Line#1. Also, if you want to sort according to a different column, change
this: Range("B2")

Hope that helps.
Write back if you have another question.


Regards,
Ryan---

 

[ryguy7272]

WHOOPS!! I posted too soon. I think you want this instead:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column <> 10 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 10))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

This is just like the last post, except for this line:
With Range(Cells(2, 1), Cells(EndData, 10))
The ‘10’ makes all data sort over to ColumnJ, which I’m sure is what you
want (I think the last sample just sorted the date up until ColumnC).

Regards,
Ryan---

 

[Vicki]

Hi Ryan! I just submitted another message because i was still confused I, I
will delete it and try your directions again, Thanks for your time. Have a
great weekend.