my script wont post, unless i hit a key..

[0o0o0]

this script below.. works it just wont post on a worksheet by itself (
automatically every minute)..

unless I hit any key every minute.. then it will post every minute.

My question is what am I missing....

heres the script.. I set it to 15 seconds just for testing purposes..

Private RunWhen As Double
Private Const cRunIntervalSeconds = 15 ' two minutes
Private Const cRunWhat = "Worksheet_Change"


Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Today = Now
x = Sheets("sheet1").Range("a65536").End(xlUp).Row + 1
Sheets("sheet1").Range("a" & x) = Range("a1")

x = Sheets("sheet1").Range("b65536").End(xlUp).Row + 1
Sheets("sheet1").Range("b" & x) = Range("b2")

x = Sheets("sheet1").Range("c65536").End(xlUp).Row + 1
Sheets("sheet1").Range("c" & x) = Range("c2")

x = Sheets("sheet1").Range("d65536").End(xlUp).Row + 1
Sheets("sheet1").Range("d" & x) = Range("c4")

x = Sheets("sheet1").Range("e65536").End(xlUp).Row + 1
Sheets("sheet1").Range("e" & x) = Range("c5")

x = Sheets("sheet1").Range("f65536").End(xlUp).Row + 1
Sheets("sheet1").Range("f" & x) = Range("c6")

x = Sheets("sheet1").Range("g65536").End(xlUp).Row + 1
Sheets("sheet1").Range("g" & x) = Range("c7")

x = Sheets("sheet1").Range("h65536").End(xlUp).Row + 1
Sheets("sheet1").Range("h" & x) = Range("c8")

x = Sheets("sheet1").Range("i65536").End(xlUp).Row + 1
Sheets("sheet1").Range("i" & x) = Range("c9")

x = Sheets("sheet1").Range("j65536").End(xlUp).Row + 1
Sheets("sheet1").Range("j" & x) = Range("c10")

x = Sheets("sheet1").Range("k65536").End(xlUp).Row + 1
Sheets("sheet1").Range("k" & x) = Range("c11")

x = Sheets("sheet1").Range("l65536").End(xlUp).Row + 1
Sheets("sheet1").Range("l" & x) = Range("c12")

x = Sheets("sheet1").Range("m65536").End(xlUp).Row + 1
Sheets("sheet1").Range("m" & x) = Range("c13")

x = Sheets("sheet1").Range("n65536").End(xlUp).Row + 1
Sheets("sheet1").Range("n" & x) = Range("c14")

x = Sheets("sheet1").Range("o65536").End(xlUp).Row + 1
Sheets("sheet1").Range("o" & x) = Range("c15")

x = Sheets("sheet1").Range("p65536").End(xlUp).Row + 1
Sheets("sheet1").Range("p" & x) = Range("c16")

x = Sheets("sheet1").Range("q65536").End(xlUp).Row + 1
Sheets("sheet1").Range("q" & x) = Range("c17")

x = Sheets("sheet1").Range("r65536").End(xlUp).Row + 1
Sheets("sheet1").Range("r" & x) = Range("c18")

x = Sheets("sheet1").Range("s65536").End(xlUp).Row + 1
Sheets("sheet1").Range("s" & x) = Range("c19")

StartTimer

End Sub






Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub



THANKS!

 

[Earl Kiosterud]

OoOoO,

You're trying to run Worksheet_Change via the OnTime method. Normally, that
proc is triggered automatically by an event -- a change to the worksheet.
Do you want the posting to happen every minute OR when a key is pressed?

 

[J.E. McGimpsey]

You can't call an event macro using OnTime - event macros are run
when their events fire (e.g., for Worksheet_Change(), when an entry
is made in the sheet).

Suggestion:

In a *regular* code module (Insert/Module):

Option Explicit
Public gdRunWhen As Double
Public Const cRunIntervalSeconds = 60 'one minute
Public Const cRunWhat = "UpdateSheet"

Public Sub StartTimer()
gdRunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime _
EarliestTime:=gdRunWhen, _
Procedure:=cRunWhat, _
Schedule:=True
End Sub

Public Sub StopTimer()
On Error Resume Next
Application.OnTime _
EarliestTime:=gdRunWhen, _
Procedure:=cRunWhat, _
Schedule:=False
End Sub

Substitute the name of your source sheet for "Sheet2" in this macro:

Public Sub UpdateSheet()
Dim oSourceSht As Worksheet
Dim i As Integer
Set oSourceSht = Sheets("Sheet2") 'change to suit
With Sheets("Sheet1")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = _
oSourceSht.Range("A1").Value
.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = _
oSourceSht.Range("B2").Value
For i = 3 To 19
.Cells(Rows.Count, i).End(xlUp).Offset(1, 0).Value = _
oSourceSht.Cells(i - 1, 3).Value
Next i
End With
StartTimer
End Sub

If all the columns have the same last row, this is a bit more
efficient:

Public Sub UpdateSheet()
Dim oSourceSht As Worksheet
Dim nLastRow As Long
Dim i As Integer
Set oSourceSht = Sheets("Sheet2") 'change to suit
With Sheets("Sheet1")
nLastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(nLastRow, 1).Value = oSourceSht.Range("A1").Value
.Cells(nLastRow, 2).Value = oSourceSht.Range("B2").Value
For i = 3 To 19
.Cells(nLastRow, i).Value = _
oSourceSht.Cells(i - 1, 3).Value
Next i
End With
StartTimer
End Sub

 

[0o0o0]

THANK YOU!!!

Ive been trying to get this to work for lol 3 years!...

but just started trying VB out 3 weeks ago, read everything asked
questions everywhere.. and you did it.. THANKS!