auto sorting help needed thank you!

[Guest]

Hello, I submitted a post the other day, I was wondering if any one
experienced could give me some advice on automatic sorting. I have two sheets
that are linked together by the last column when the number in the last
column changes everyday with new information pasted into sheet two the
numbers are either higher or lower. In order to keep the positions in from
highest to lowest, the ideal situation for this database is to have the sheet
automatically sort when that last column changes. changes made from J6 - J40
(last column) and when that changes entire row (stock ticker stock etc) needs
to be sorted as well. Is that an "if" function. I tried to add a code (right
click on sheet 1) and view code and entered what I was told but it hasnt work

This was my first discussion group message abotu this topic:
"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."



Thank you in advance!!

 

[Guest]

Put the following macro in the worksheet event code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set rj = Range("J6:J40")
Set rall = Range("A6:IV256")
If Intersect(Target, rj) Is Nothing Then Exit Sub
Application.EnableEvents = False
rall.Sort Key1:=Range("J6")
Application.EnableEvents = True
End Sub

Once installed, any changes to J6 thru J40 will automatically trigger a
re-sort of rows 6 thru 40.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Guest]

Vicki, did you ever get this working? If you want to send me a copy of your
workbook, I will try to do it within the next couple of days.


Regards,
Ryan---
(e-mail address removed)