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
Shatin wrote:
>
> 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
Similar Threads
