Moving "sort by column"
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 |
Moving "sort by column"
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 |
All times are GMT +1. The time now is 03:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com