Moving "sort by column"

[brian]

I am trying to write a macro to automatically sort a small section of a
database of stock prices (see below) by referencing the column where the
current date is located, currently at "AW5".

Code 17/07/06 18/07/06 19/07/06
AAC 1.80 1.85 1.86
AAE 0.64 0.63 0.63
AAI 42.75 42.75 42.75
AAM 0.24 0.22 0.21
AAO 0.14 0.15 0.15

At this point, Excel generated the following codes for me:

Selection.Sort Key1:=Range("AW5"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

However, after adding a new set of column data the next day, the date
location changes to "AX5", and the "sort by column" is now one column to the
right. How can I modify the macro to automatically sort by the new "date
column" ? Any help would be much appreciated.

TIA
Brian

[FSt1]

hi,
i assume that since you add a column each day that all the columns to the
right are blank. if so then this may do what you want.

sub sortmovecol()
Range("IV5").End(xlToLeft).Select
ActiveSheet.UsedRange.Sort Key1:=Selection, Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end sub

regards
FSt1

"brian" wrote:

> I am trying to write a macro to automatically sort a small section of a
> database of stock prices (see below) by referencing the column where the
> current date is located, currently at "AW5".
>
> Code 17/07/06 18/07/06 19/07/06
> AAC 1.80 1.85 1.86
> AAE 0.64 0.63 0.63
> AAI 42.75 42.75 42.75
> AAM 0.24 0.22 0.21
> AAO 0.14 0.15 0.15
>
> At this point, Excel generated the following codes for me:
>
> Selection.Sort Key1:=Range("AW5"), Order1:=xlAscending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> However, after adding a new set of column data the next day, the date
> location changes to "AX5", and the "sort by column" is now one column to the
> right. How can I modify the macro to automatically sort by the new "date
> column" ? Any help would be much appreciated.
>
> TIA
> Brian
>
>
>