Copy/Paste to Next Row

March 21, 2022

My Worksheet_Calculate event works well. However when it pastes it finds the next open row on sht2 then once the event is trigger again it re-pastes over the original row it pasted the first time. Goal is to copy/paste A39:Q39 in the next available row each time the event is triggered on sht2 and not overwrite the previous paste.

Private Sub Worksheet_Calculate()

Dim timeCells As Range, i As Integer

If Worksheets("Dashboard").ToggleButton1.Value = True Then

On Error GoTo SafeExit
Application.EnableEvents = False

'TimeLog
Set timeCells = Me.Range("D4:D393")
Set sht1 = ThisWorkbook.Sheets("Dashboard")
Set sht2 = ThisWorkbook.Sheets("Log")
Set cpyRng = sht1.Range("A39:Q39")
Set rngLogTargetBeginningCell = sht2.Cells(Rows.Count, 1).End(xlUp)
Set rngLastCellSelection = Selection

Application.ScreenUpdating = False ' Stop Updating Graphic during data copy

'TimeLog
For i = 1 To UBound(myArrTimeLog)
    If timeCells(i, 1).Value <> myArrTimeLog(i, 1) Then
        cpyRng.Copy
        rngLogTargetBeginningCell.Offset(0, 1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False ' Remove the copy area marker
        rngLastCellSelection.Select    ' reselect the old cell
        Application.ScreenUpdating = True  ' update graphics again
    End If
Next i
End If

SafeExit:
Application.EnableEvents = True
PopulateTimeLog
End Sub

>Solution :

Well that is a lot of code for a simple task.
Your code copies in the exact same position as before, because you tell your code to start one column next to cell 1 of your last row.
Change the offset in your paste line to

rngLogTargetBeginningCell.Offset(1, 0).PasteSpecial xlPasteValues

and you could be good to go.
Since there is more in your code I can’t interpret without more information, I can’t tell you for sure, that it works… but it is a likely try…