VBA Application.OnTime

Question

Hello,

I have two different macros that I need to run simultaneously. One is updating a data set, the other updates an .htm page. I'm guessing I need to combine the code into a single macro as both macros using copy and paste functions.

The code for the two macros is below. I have tried various combinations of inserting the code from the second set of code into the first and am getting error messages...somethings conflicting. Any help? And or alternate solutions?

Sub AddToPriceHistory()

Application.ScreenUpdating = False

dtime = Now + TimeValue("00:00:01")
Application.OnTime dtime, "AddToPriceHistory"
Range("A4:bk4").Select
Selection.Copy
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

.htm macro:

Sub AddToPriceHistory()

Application.ScreenUpdating = False

dtime = Now + TimeValue("00:00:01")
Application.OnTime dtime, "AddToPriceHistory"
Calculate
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\Users\Michael\Documents\Htm Test.htm", "Sheet1", "$E$4:$J$4", xlHtmlStatic _
, "Book1_19935", "")
.Publish (True)
.AutoRepublish = False
End With
End Sub

Thanks in Advance.

MCH

Answer 1

Running a procedure to create a file every second is not a good idea - if the file is not ready yet you'd get conflicts. Try this:

Sub AddToPriceHistory()

    Application.ScreenUpdating = False

    dtime = Now + TimeValue("00:01:00")

    Application.OnTime dtime, "AddToPriceHistory"

    Range("A4:BK4").Copy

    Range("A9").PasteSpecial Paste:=xlPasteValues

    Range("A9").PasteSpecial Paste:=xlPasteFormats

    Application.CutCopyMode = False

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Calculate

    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _

            "C:\Users\Michael\Documents\Htm Test.htm", "Sheet1", _

            "$E$4:$J$4", xlHtmlStatic, "Book1_19935", "")

        .Publish True

        .AutoRepublish = False

    End With

    Application.ScreenUpdating = True

 End Sub

Answer 2

I think I got it working.