First run this, then wait 3 minutes
Sub CopyPaste_Main()
'If HasNew Then CopyPaste_Recent
MSgBox "Running CopyPadte_Main at " & Now
Application.OnTime Now + TimeSerial(0, 1, 0), "CopyPaste_Main"
'Date +TimeSerial(24, 0, 1) is 1 second after midnight tomorrow morning.
End Sub
Then
Sub CopyPaste_Main()
If HasNew Then MSgBox "HasNEw Was Called"
Application.OnTime Now + TimeSerial(0, 1, 0), "CopyPaste_Main"
'Date +TimeSerial(24, 0, 1) is 1 second after midnight tomorrow morning.
End Sub
Function HasNew() As Boolean
'HasNew = Not Sheets("t").Range("A:A").Find("New") Is Nothing
HasNew = True
End Function
Then
Sub CopyPaste_Main()
If HasNew Then MsgBox "HasNew found the word New"
Application.OnTime Date + TimeSerial(0, 1, 0), "CopyPaste_Main"
'Date +TimeSerial(24, 0, 1) is 1 second after midnight tomorrow morning.
End Sub
Function HasNew() As Boolean
HasNew = Not Sheets("t").Range("A:A").Find("New") Is Nothing
End Function
Then
Sub CopyPaste_Recent()
Dim lastRow As Long
lastRow = Sheets("y").Cells(Rows.Count, "B").End(xlUp).Row + 1 ' then next free row in sheet y
MsgBox"The next free row in sheet y is " & LastRow
End Sub
Finally
Sub CopyPaste_Recent()
Dim FirstCel As Range
Dim LastCel As Range
With Sheets("t")
Set First Cel = .Range("A:A").Find("New").Offset(0, 1)
Set LastCel = .Cells(Rows.Count, "B").End(xlUp).End(xlToRight)
MsgBox"The Range to Copy is " & Range(FirstCel, LastCel).Address
End Sub
Note that those simple Finds might not work every time. See the Help on Find for more complex versions that will.