HELP! Run a macros on the hour, every hour automatically...

[bapcki1]

Hi Everyone!

I want to run a macros automatically on the hour, every hour.

Could anyone help with my following problem please?

I'm relatively new to VBA and I dont want someone to write a program for me so links to articles (and possibly a li'l bit of VBA code (if necessary)) would be great!

So far I have got code to update the =now() formula every minute:

ThisWorkBook:

Private Sub Workbook_Open()
    Dim CalcTime As String
    CalcTime = Format(WorksheetFunction. _
    Ceiling(Timer, 60) / 86400, "long time")
    Application.OnTime TimeValue(CalcTime), "UpdateTime"
End Sub

Module:

Public Sub UpdateTime()
    Range("I11").Calculate
    Application.OnTime Time + _
    TimeSerial(0, 1, 0), "UpdateTime"
End Sub

then by converting the time into minutes and using 24 =IF formulas, I have a cell, ( K36 ) that will = 1 on the hour, every hour.

With great help from Dave 3009, I have code to automatically run a macros IF cell K36 = 1 :

Code Module for the Sheet in question:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$K$36" Then Exit Sub
If Target.Value = 1 Then
    copytohere
End If
End Sub

ThisWorkBook:

Sub copytohere()
If ThisWorkbook.Sheets("Sheet1").Range("K36").Value = "1" Then
    Range("A1").Copy
    Range("E1").PasteSpecial xlPasteValues
    Range("A1").ClearContents
End If
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "0"
End Sub

Both sets of VBA code work perfectly on their own but I cant seem to get them to work together. Can anybody help please?


Paddy

 

[tusharm]

If I understand the problem correctly, see
Schedule a Subroutine on a Periodic Basis Each Day
http://www.tushar-mehta.com/publish_train/xl_vba_cases/schedule_task_daily.htm

Hi Everyone!

I want to run a macros automatically on the hour, every hour.

Could anyone help with my following problem please?

I'm relatively new to VBA and I dont want someone to write a program for me so links to articles (and possibly a li'l bit of VBA code (if necessary)) would be great!

So far I have got code to update the =now() formula every minute:

ThisWorkBook:

Private Sub Workbook_Open()
    Dim CalcTime As String
    CalcTime = Format(WorksheetFunction. _
    Ceiling(Timer, 60) / 86400, "long time")
    Application.OnTime TimeValue(CalcTime), "UpdateTime"
End Sub

Module:

Public Sub UpdateTime()
    Range("I11").Calculate
    Application.OnTime Time + _
    TimeSerial(0, 1, 0), "UpdateTime"
End Sub

then by converting the time into minutes and using 24 =IF formulas, I have a cell, ( K36 ) that will = 1 on the hour, every hour.

With great help from Dave 3009, I have code to automatically run a macros IF cell K36 = 1 :

Code Module for the Sheet in question:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$K$36" Then Exit Sub
If Target.Value = 1 Then
    copytohere
End If
End Sub

ThisWorkBook:

Sub copytohere()
If ThisWorkbook.Sheets("Sheet1").Range("K36").Value = "1" Then
    Range("A1").Copy
    Range("E1").PasteSpecial xlPasteValues
    Range("A1").ClearContents
End If
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "0"
End Sub

Both sets of VBA code work perfectly on their own but I cant seem to get them to work together. Can anybody help please?


Paddy