Scheduled VBA Sorting Macro

[tech_boy]

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

[jaclaz]

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-Second

jaclaz

[tech_boy]

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:=xlSortNormal

End Sub

Would I then use the following code within the same Module as my Macro?

Public dTime As Date

Sub Run_Macro()

dTime = Now + TimeValue("00:15:00")

Application.OnTime dTime, "Run_Macro"

End Sub

As well as having the following code in my main workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "Run_Macro", , False

End Sub

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:15:00"), "Run_Macro"

End Sub

Forgive the amount of code here, I'm just trying to get my head around all of it!

Thanks again,

- Jon

[jaclaz]

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 TimeToRun

Sub auto_open()

Call ScheduleCopyPriceOver ScheduleSort

End Sub

Sub ScheduleCopyPriceOver() ScheduleSort()

TimeToRun = Now + TimeValue("00:00:01") ("00:15:00")

Application.OnTime TimeToRun, "CopyPriceOver" "sort"

End Sub

Sub CopyPriceOver()sort()

Calculate

Range("c7").Value = Range("d7").Value

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:=xlSortNormal

Call ScheduleCopyPriceOverScheduleSort

End Sub

Sub auto_close()

On Error Resume Next

Application.OnTime TimeToRun, "CopyPriceOver" "sort", , False

End Sub

BEFORE 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