I got rid of the many versions of this sheet and downloaded version 2.2 again which has the following...
Sub check1() Dim lRow As Long Application.ScreenUpdating = 0 With Sheets("Sheet1").Cells(1).CurrentRegion With .Offset(4).Resize(.Rows.Count - 4, 16) .Sort .Columns(1) End With x = .Value2 End With With Sheets("race1") If .[a61] = vbNullString Then lRow = 61 Else lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 15 - .[c20] End If .Cells(lRow, 1).Resize(UBound(x, 1), UBound(x, 2)) = x .Columns(1).AutoFit .Activate ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = lRow End With End Sub
The accompanying WORKSHEET 1 code
Private Sub Worksheet_Change(ByVal Target As Range) If Not IsError(Application.Match([c2], [Times], 0)) Then Application.EnableEvents = 0 check1 Application.EnableEvents = 1 End If End Sub
The old problem is persisting when sheet 1 is live with the live time changing in cell C2, The check1 code will execute on time according to the schedule in 'race1' sheet the value in A20 will turn from 0 to 1 butthen when reverting from 1 to 0 after the first paste it quickly pastes again and I am left with this problem of 2 pastes rather than 1.
If I disable the live feed into 'sheet1' and manually enter time values that will match the schedule times on 'race1' sheet the check 1 code will fire once and work perfectly every time with a single paste.
KJ, I can only assume that if you have got this same code to paste once the problem is with the feed when it is live and changing. When I manually enter a time in C2 and it works fine and pastes once the 1 value in cell A20 on race1 sheet remains.Is the problem perhaps that when live and the time moves on past the schedule time again aftyer firing the macro, the change in cell A20 from 1 back down to zero is firing check one for a 2nd time.
before trigger time A20 =0 when hits trigger time changes to 1 and fires check 1
after trigger time A20 reverts back to 0 from 1 and fires for the 2nd time
Amazing how something that seemed so simple is proving to be so awkward!
I appreciate you have spent plenty of time on this already, If you think of anything new I would be most grateful, I will also be trying to see if I can find a similar problem on the net that was overcome.