Hi Nick,
Your code looks helpful. However I can't access the code for the buttons that call the macros and as a result I have failed to integrate the code into Excel for use. I'll appreciate your advice on this. thank you

Hi Nick,

I have been struggling to find someone that knows how to explain to me how to write VBA for a cell to show the average pieces per hour or parts we run.
It needs to look through entered data in a column every hour between set criteria times (example 5am to 5:59am) add up all the parts and post the average in the cell so everyone can see it. So it will essentially be a running average posting in this cell.

I stumbled across your VBA Tips #9 talking about a running a code every hour, minute or second and thought you would be the guy I should be talking to.

Any help you can lend on this would be greatly appreciated!!

Thank You,
Dan Stokes

Hello Nick,

I am new here. Sorry if I am posting this in a wrong place.

The above code works good and it updates the result in same cell.

My question is how do we update the formula for every minute and retrieve the answer in a sequence of cells along with the timestamp till we click the stop button.

Awaiting for your reply.

Regards,
HarishKannan.

Nick:

I like the basic concept of your code.. However, the auto close component did not work for me. Below, I have made some improvements. I kept your idea of using Macro 1 to launch Macro 2 to launch Macro 3 to launch Macro 2.

Its a great way to initiate a loop. Thank you for that.

I'm not sure your Macro 4 (to end the loop) actually works. It did not for me anyway.

What I've done is added is a way to gracefully exit from the loop.

I am using Cell A1 to contain a value (0, 1, 2)

The macro uses several IF statements to check if it should continue. It only continues if A1 = 1.

The macro is designed so that you can add a start and stop button to the spreadsheet. Its also a nice touch to add some text in one of the spreadsheet cells to indicate if the auto refresh macro is running, aborting or stopped.

The start button launches the first macro. The stop button runs the last macro.

Naturally the macros populate values 0, 1 and 2 into cell A1.

The IF statements cause the loop to end if A1 is not equal to 1.

It can take a few seconds for the macro to actually end because it still needs to complete one more refresh and wait cycle. For this reason Cell A1 is set to equal 2 until the macro actually exits the loop. This does not help the macro to stop, it just puts some signaling ont o the spreadsheet so that you can tell the user if the macro has actually stopped yet. This is important because closing a spreadsheet while a Macro is running is not pretty.

To let your user know what state the macro is in,
the following formula can be placed in any cell near the two buttons.

=IF(A1=1,"Refresh is Running", IF( A1 = 2, "Stop Command Sent", "Refresh is Stopped"))

Some simple conditional formatting can color the text green, orange and red.

Below is the macro. It should be easy to cut and paste into any Macro Module.

******************************************************
Dim TimeToRun

Sub Auto_Refresh()
'Create a "Start" button on the spreadsheet to launch this Macro
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select

'refresh once before cycle starts
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True

Call Schedule_Refresh

End Sub

Sub Schedule_Refresh()

TimeToRun = Now + TimeValue("00:00:10")

'Start the loop if A1 =1
If Range("A1") = 1 Then
Application.OnTime TimeToRun, "Refresh"
End If

'If A1 = 2 it means the abort process was started and has now ended
'So set A1 = 0 so that the spreadsheet knows the macro has actually stopped
If Range("A1") = 2 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
Range("A2").Select

End If

End Sub

Sub Refresh()
'Refresh everything
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True

'Restart the loop is A1 = 1
If Range("A1") = 1 Then
Call Schedule_Refresh
End If

'End the loop'If A1 = 2 it means the abort process was started and has now ended
'So set A1 = 0 so that the spreadsheet knows the macro has actually stopped

If Range("A1") = 2 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
Range("A2").Select
End If

End Sub

Sub autoclose()
'Create a "Stop" button on the spreadsheet to launch this Macro

'Set A1 equal to 2 to signal the loop to abort
Range("A1").Select
ActiveCell.FormulaR1C1 = "2"
Range("A2").Select
End Sub

I am unable to successfully include the following macro to get it to run every 1 min, appreciate your guidance on how to include my code.

Sub Test2()

Dim LastCol As String

With Worksheets("Sheet2")
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Address
.Range(Range("B100"), LastCol).Copy

.Range("C2", Range("C2").Offset(LastRow - FirstRow, 0)).PasteSpecial xlPasteValues

End With
End Sub

Nick,

First of all thanks for this great simple piece of code.
I am fairly new to Macros.

So here is my problem. I have a sheet (Raw Data). I import some data from a web query and set its refresh property to every 15min.
The another sheet (Output data) I copy only the fields i require and trim to required lengths etc. that works fine.

My Marco, i set it to run every 15min and 15 sec (to give some time for the web query to update and then run the macro.

I have a start and stop button assigned to Start and Termination functions.
So when I press start i see the Macro starting to work.
It works for a while and stops suddenly (and different times, its not regular intervals) and starts working by it self again.

So for eg I had once, it worked till 15:49 and then two hours it did nothing and started again around 18:00 something. so for those two hours the web query refreshed but macro did not run!

Any idea why this is? Thanks in advance.
Regards

My Macro is as follows:

Dim runtime
Sub Start()
'' Start Recording vessel positions
'Call Extractor
End Sub

Sub Duration()
'Define Time interval required
runtime = Now + TimeValue("00:15:15")
Application.OnTime runtime, "Extractor"
End Sub

Sub Extractor()
'
' Retrives required information and copies to Table

Range("A6:G6").Select
Selection.Copy
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A8").EntireRow.Select
Selection.Insert Shift:=x1Down
Call Duration
End Sub

Sub Termination()
'To stop recording and terminate process
Application.OnTime runtime, "Extractor", , False
End Sub

This was very helpful. However, for some reason I cannot get this to work for what I am trying to do. Here is my code below;

-------------------------------------------------
Dim TimeToRun

Sub auto_open()
Call autorun
End Sub

Sub autorun()
TimeToRun = Now + TimeValue("00:00:15")
application.OnTime TimeToRun, "datafeed"
End Sub

Sub datafeed()
application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:= _
"C:\Users\Carella Home\Desktop\DSA Data Feed.txt", FileFormat:=xlText, _
CreateBackup:=False
Call autorun
End Sub

Sub auto_close()
application.OnTime TimeToRun, "datafeed", , False
End Sub

Sub eodsave()
application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:= _
"C:\Users\Carella Home\Desktop\DSA Data Feed.xlsm", FileFormat:=52, CreateBackup:=False
End Sub
-------------------------------------------

It works fine and saves the file that I need it to save as a text file every 15 seconds. The problem is that when I close the file, I get the following message;

Run-time error '1004':

Method'OnTime' of object '_Application' failed

Can anyone tell me what I am doing wrong here? I essentially need my excel file to spit out a text document save of itself every 15 seconds.

It's really helpful

Nick

I've tried to follow the advice you have posted to run a monitor of open actions in a request system.

I have encountered an error when the Application.OnTime function attempts to access my code as below:

"Cannot run the macro "O:\Engineering Requests\Request Monitor.xlsm'!RefreshRequest'. The macro may not be available in this workbook or all macros may be disabled."

As far as i am aware, all macros & ActiveX content is enabled and the code is writen within the same workbook.

Any suggestions how I can resolve the error?