tech_boy Posted August 1, 2011 Share Posted August 1, 2011 Hello all,I have a simple macro that sorts a column in MS Excel in descending order. Is it possible to automatically schedule this to happen every 30mins or so?Thanks in advance, - Jon Link to comment Share on other sites More sharing options...
jaclaz Posted August 1, 2011 Share Posted August 1, 2011 I have a simple macro that sorts a column in MS Excel in descending order. Is it possible to automatically schedule this to happen every 30mins or so?If it is VBA, maybe it is better suited here:http://www.msfn.org/board/forum/116-microsoft-office-97-2010/(this forum is VB or VBS)However you can use the Application.OnTime allright:http://excelexperts.com/VBA-Tips-Run-Code-Every-Hour-Minute-or-Secondjaclaz Link to comment Share on other sites More sharing options...
tech_boy Posted August 1, 2011 Author Share Posted August 1, 2011 Thanks for the advice So if I have a macro by the name of "sort" which looks like this:Sub sort()'' Sort Macro' Macro recorded 01/08/2011 by jmoore10' Range("C3:C22").Select ActiveCell.Offset(-1, 0).Range("A1:C21").sort Key1:=ActiveCell.Offset(0, 2). _ Range("A1"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormalEnd SubWould I then use the following code within the same Module as my Macro?Public dTime As DateSub Run_Macro()dTime = Now + TimeValue("00:15:00")Application.OnTime dTime, "Run_Macro"End SubAs well as having the following code in my main workbook:Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "Run_Macro", , FalseEnd SubPrivate Sub Workbook_Open() Application.OnTime Now + TimeValue("00:15:00"), "Run_Macro"End SubForgive the amount of code here, I'm just trying to get my head around all of it!Thanks again, - Jon Link to comment Share on other sites More sharing options...
jaclaz Posted August 1, 2011 Share Posted August 1, 2011 (edited) Thanks for the advice So if I have a macro by the name of "sort" which looks like this:Well, it's not that difficult.The given page has also a downloadable example, which contains a single Module, which contains the code, I would experiemnt with it, all you have to do is replace Sub names where appropriate and replace the "main" Sub "CopyPriceOver" with your "sort" thingy.Dim TimeToRunSub auto_open() Call ScheduleCopyPriceOver ScheduleSortEnd SubSub ScheduleCopyPriceOver() ScheduleSort() TimeToRun = Now + TimeValue("00:00:01") ("00:15:00") Application.OnTime TimeToRun, "CopyPriceOver" "sort"End SubSub CopyPriceOver()sort() Calculate Range("c7").Value = Range("d7").ValueRange("C3:C22").SelectActiveCell.Offset(-1, 0).Range("A1:C21").sort Key1:=ActiveCell.Offset(0, 2). _Range("A1"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, _MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Call ScheduleCopyPriceOverScheduleSortEnd SubSub auto_close() On Error Resume Next Application.OnTime TimeToRun, "CopyPriceOver" "sort", , FalseEnd SubBEFORE intoducing any variation....Your Sub, unless I am mistaken is called "sort" so the reference to "Run_Macro" seems like meaning "A suffusion of yellow" (or a self-referencing infinite loop )jaclaz Edited August 1, 2011 by jaclaz Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now