How to activate a worksheet at a set time

[Shatin]

I have a file with several worksheets which need to be updated with data
from the web. I have already written a macro ("UpdateSheet") which can
achieve this task. This macro requires the name of the worksheet which calls
the macro to work properly.

I'd like to automate the task and have written a macro as below. The idea
is that one second before the scheduled update time, the proper worksheet
will first be activated (in case I am working on another worksheet or even
another workbook), and then the update will carried out:

------
Sub AutoUpdate()

Dim SheetName As String
Dim UpdateTime As Date

SheetName = ActiveSheet.Name
UpdateTime = Range("C38").Value

Application.OnTime TimeValue(UpdateTime - TimeValue("0:00:01")),
ThisWorkbook.Sheets(SheetName).Activate
Application.OnTime TimeValue(UpdateTime), "UpdateSheet"

End Sub
------

The problem is that the following line isn't working properly:

Application.OnTime TimeValue(UpdateTime - TimeValue("0:00:01")),
ThisWorkbook.Sheets(SheetName).Activate

At the scheduled time I get this error message from Excel:

The macro "<<filename.xls>>'!True' cannot be found -- <<filename.xls>> is
the full path filename. What I don't understand is why is Excel looking for
this mysterious True macro???

Incidentally, when I tried to debug.print at a set time, I get the same
error message.

Can somebody tell me what I have done wrong?

Thanks!

 

[Dave Peterson]

I'd try:

Sub AutoUpdate()

Dim SheetName As String
Dim UpdateTime As Date

SheetName = ActiveSheet.Name
UpdateTime = Range("C38").Value

Application.OnTime UpdateTime, "UpdateSheet"

End Sub

UpdateTime already looks like a time. It doesn't need TimeValue().

You may want to take a look at Chip Pearson's notes on .ontime:
http://www.cpearson.com/excel/OnTime.aspx