Hi all....I intend to run the macro continuously either by creating a loop of macro or any other way of automatically sorting the columns continuously.....please help .....thanks in advance
About macro-
i have an excel macro which simply sorts few columns (numbers in descending and ascending manner) and assigned a button to run the macro.
Few things...a) the excel is DDE linked so the the numbers are updated every second....
b) this macro was recorded so i don't take any credits for the code. My understanding is of very basic level...
I have given macro name"sortinoplusab" ....it sorts four columns- B (sort largest to smallest), Column AC- (sort smallest to largest , Column BI & CN both (sort largest to smallest)...the macro that was recorded is...
Sub sortinoplusab()
'
' sortinoplusab Macro
'
'
Range("B3").Select
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("B3"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sortino").Sort
.SetRange Range("A3:X228")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AC3").Select
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("AC3"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sortino").Sort
.SetRange Range("AB3:AY228")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("BI3").Select
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("BI3"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sortino").Sort
.SetRange Range("BB3:BV203")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("CN3").Select
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("CN3"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sortino").Sort
.SetRange Range("BX3:CN203")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("CV18").Select
End Sub
Please in future place your code within code tags - read the forum rules that explains how to use the code tags and our forum rules.
Place this code into a module and run the macro called runSort - it will run the sort every 1 second continuously until you close the workbook.
Sub runSort() Application.OnTime Now + TimeValue("00:00:01"), "sortinoplusab" End Sub Sub sortinoplusab() Application.ScreenUpdating = False ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("B3"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sortino").Sort .SetRange Range("A3:X228") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("AC3"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sortino").Sort .SetRange Range("AB3:AY228") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("BI3"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sortino").Sort .SetRange Range("BB3:BV203") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sortino").Sort.SortFields.Add Key:=Range("CN3"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sortino").Sort .SetRange Range("BX3:CN203") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Application.ScreenUpdating = True runSort End Sub
Hope this helps.
Anthony
Pack my box with five dozen liquor jugs
PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated”
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks