I have no idea - it works for me. Did you save the workbook then re-open it?
Yes I did... I even when further and did the work book using your same formula without changing or adding any sheets / names and it did not work....
Yes I did... I even when further and did the work book using your same formula without changing or adding any sheets / names and it did not work....
Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("D 0-1")
If Now - Date >= TimeValue("00:00:00") And Now - Date <= TimeValue("00:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 1-2")
If Now - Date >= TimeValue("01:00:00") And Now - Date <= TimeValue("01:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 2-3")
If Now - Date >= TimeValue("02:00:00") And Now - Date <= TimeValue("02:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 3-4")
If Now - Date >= TimeValue("03:00:00") And Now - Date <= TimeValue("03:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 4-5")
If Now - Date >= TimeValue("04:00:00") And Now - Date <= TimeValue("04:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 5-6")
If Now - Date >= TimeValue("05:00:00") And Now - Date <= TimeValue("05:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 6-7")
If Now - Date >= TimeValue("06:00:00") And Now - Date <= TimeValue("06:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 7-8")
If Now - Date >= TimeValue("07:00:00") And Now - Date <= TimeValue("07:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 8-9")
If Now - Date >= TimeValue("08:00:00") And Now - Date <= TimeValue("08:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 9-10")
If Now - Date >= TimeValue("09:00:00") And Now - Date <= TimeValue("09:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 10-11")
If Now - Date >= TimeValue("10:00:00") And Now - Date <= TimeValue("10:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 11-12")
If Now - Date >= TimeValue("11:00:00") And Now - Date <= TimeValue("11:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 12-13")
If Now - Date >= TimeValue("12:00:00") And Now - Date <= TimeValue("12:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 13-14")
If Now - Date >= TimeValue("13:00:00") And Now - Date <= TimeValue("13:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 14-15")
If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("14:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 15-16")
If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("15:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 16-17")
If Now - Date >= TimeValue("16:00:00") And Now - Date <= TimeValue("16:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 17-18")
If Now - Date >= TimeValue("17:00:00") And Now - Date <= TimeValue("17:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 18-19")
If Now - Date >= TimeValue("18:00:00") And Now - Date <= TimeValue("18:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 19-20")
If Now - Date >= TimeValue("19:00:00") And Now - Date <= TimeValue("19:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 20-21")
If Now - Date >= TimeValue("20:00:00") And Now - Date <= TimeValue("20:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 21-22")
If Now - Date >= TimeValue("21:00:00") And Now - Date <= TimeValue("21:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 22-23")
If Now - Date >= TimeValue("22:00:00") And Now - Date <= TimeValue("22:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 23-24")
If Now - Date >= TimeValue("23:00:00") And Now - Date <= TimeValue("23:59:59") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
End Sub
Rich (BB code):Public dTime As Date Sub MyMacro() dTime = Now + TimeValue("00:00:05") Application.OnTime dTime, "MyMacro" With Sheets("D 0-1") If Now - Date >= TimeValue("00:00:00") And Now - Date <= TimeValue("00:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 1-2") If Now - Date >= TimeValue("01:00:00") And Now - Date <= TimeValue("01:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 2-3") If Now - Date >= TimeValue("02:00:00") And Now - Date <= TimeValue("02:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 3-4") If Now - Date >= TimeValue("03:00:00") And Now - Date <= TimeValue("03:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 4-5") If Now - Date >= TimeValue("04:00:00") And Now - Date <= TimeValue("04:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 5-6") If Now - Date >= TimeValue("05:00:00") And Now - Date <= TimeValue("05:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 6-7") If Now - Date >= TimeValue("06:00:00") And Now - Date <= TimeValue("06:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 7-8") If Now - Date >= TimeValue("07:00:00") And Now - Date <= TimeValue("07:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 8-9") If Now - Date >= TimeValue("08:00:00") And Now - Date <= TimeValue("08:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 9-10") If Now - Date >= TimeValue("09:00:00") And Now - Date <= TimeValue("09:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 10-11") If Now - Date >= TimeValue("10:00:00") And Now - Date <= TimeValue("10:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 11-12") If Now - Date >= TimeValue("11:00:00") And Now - Date <= TimeValue("11:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 12-13") If Now - Date >= TimeValue("12:00:00") And Now - Date <= TimeValue("12:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 13-14") If Now - Date >= TimeValue("13:00:00") And Now - Date <= TimeValue("13:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 14-15") If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("14:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 15-16") If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("15:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 16-17") If Now - Date >= TimeValue("16:00:00") And Now - Date <= TimeValue("16:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 17-18") If Now - Date >= TimeValue("17:00:00") And Now - Date <= TimeValue("17:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 18-19") If Now - Date >= TimeValue("18:00:00") And Now - Date <= TimeValue("18:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 19-20") If Now - Date >= TimeValue("19:00:00") And Now - Date <= TimeValue("19:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 20-21") If Now - Date >= TimeValue("20:00:00") And Now - Date <= TimeValue("20:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 21-22") If Now - Date >= TimeValue("21:00:00") And Now - Date <= TimeValue("21:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 22-23") If Now - Date >= TimeValue("22:00:00") And Now - Date <= TimeValue("22:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With With Sheets("D 23-24") If Now - Date >= TimeValue("23:00:00") And Now - Date <= TimeValue("23:59:59") Then .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value End If End With End Sub
Dear GoV...
Good morning...
I copy - pasted these codes and nothing happens yet.... It begins populating the data in its specific timed sheet, but it just fills the first row, and then a note pops up and says: Compile Error: Expected: end of statement.
Any clues????
Thanks
JC
I really have no idea - the code compiles and runs fine for me.
I suggest that you hire a professional to sort this out - see http://www.mrexcel.com/consult.shtml
Thanks... I'll do that... but I found that I had some old codes in one of the sheets. I removed it and now it says the following:
The Macro "C:\Docyments and Settings\..........!MyMacro' cannot be found.
If I ignore this message and hit run macro again, it will fill a line of data and then I'll get this message again...!!!
Any idea?
Thanks
JC