Excel file which listed new for any update information

[klpw]

Hi guys,

I'm creating a worksheet name 't' in which the data is extracted from the server. When server adds new information, new row of these information needs to be appeared in the 't' worksheet at the last row and shows the word "new" in first column. User can update new information from the 't' worksheet by column and the word "new" needs to be shown in first column as well. The word "new" needs to be amended, which means today's word "new" only can last one day. After one day, it won't be "new" again. At the same time, I would like to create another worksheet which named as 'y'. In 'y' worksheet, all the data in 'y' worksheet will be automatically transferred to 'y' worksheet at 12am everyday which is being updated everyday as well. I've started with some code as below. However, it doesn't seem to work.



Sub copypaste_RECENT()

Sheets("t").Range("B:V").Copy Destination:=Sheets("y").Range("B:V")

Application.OnTime Now + TimeSerial(24, 0, 0), "copypaste_RECENT"

End Sub


Can anyone help? Thanks in advanced.

[SamT]

All OnTIme subs must be in a standard Module

That sub should be

Sub CopyPaste_Main()

If HasNew Then CopyPaste_Recent
Application.OnTime Date + TimeSerial(24, 0, 1), "CopyPaste_Main"
'Date is Now without the time
'Date +TimeSerial(24, 0, 1) is 1 second after midnight tomorrow morning.
End Sub

Then you need

Function HasNew() As Boolean
HasNew = Not Sheets("t").Range("A:A").Find("New") Is Nothing
End Function

'And
Sub CopyPaste_Recent()
'Find first NewRow
'Copy NewRows to bottom to Sheet y
'Clear Contents Column A of "New Row"
End Sub

[klpw]

Hi SamT, I've put all the above code in standard module. However, it doesn't run in the time which I specified. Would you mind helping me figure out my issue with the code below? Thanks in advance.



Option Explicit
Sub CopyPaste_Main()

If HasNew Then CopyPaste_Recent
Application.OnTime Date + TimeSerial(17, 38, 0), "CopyPaste_Main"
'Date is Now without the time
'Date +TimeSerial(24, 0, 1) is 1 second after midnight tomorrow morning.
End Sub
Function HasNew() As Boolean
HasNew = Not Sheets("t").Range("A:V").Find("New") Is Nothing
End Function

'And
Sub CopyPaste_Recent()
Dim lastRow As Long
lastRow = Sheets("y").Range("A100000").End(xlUp).Row + 1 ' then next free row in sheet2
Sheets("t").Range("A:V").Copy Destination:=Sheets("y").Range("A:V")
End Sub

[SamT]

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.

[SamT]

(ST: Edited for relevance.)

Find Method

Find method as it applies to the Range object.

Finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Doesn’t affect the selection or the active cell.


expression.Find(What, After, SearchOrder, SearchDirection)

expression Required. An expression that returns a Range object.

What Required Variant. The data to search for. Can be a string or any Microsoft Excel data type.

After Optional Variant. The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range.

SearchOrder Optional Variant. Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.

SearchDirection Optional XlSearchDirection. The search direction.

XlSearchDirection can be one of these XlSearchDirection constants.
xlNext default
xlPrevious