Add to Next Empty Row

[Tech56]

Hi,

I made some transactions for August in rows 29 to 46 (Transactions sheet). I clicked the 'Add This Month's Recurring Transactions' to add September's recurring. It pasted to the bottom instead of starting in row 47. Can the code be modified to find the next blank row instead?

Thank You

Sub MoveTransX()
    Dim i As Long, lr As Long, lr2 As Long
    Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Recurring Log")
Set s2 = Sheets("Transactions")
Call Unprotect_Transactions


'check if already done for this month
If Month(Date) = Month(Range("J3")) Then
    MsgBox "You have already added these recurring transactions for this month."
    Call Protect_Transactions
    Exit Sub
Else
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 4 To lr
        If s1.Range("B" & i) <> "" Then
            s1.Range("A" & i & ":I" & i).Copy
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            s2.Range("A" & lr2).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    'store when last run
    s2.Range("J3") = Date
    MsgBox "This month's recurring transactions have been added."
    Call Protect_Transactions
End If
End Sub

 

[Marc L]

Hi !​

​

Try If s1.Range("B" & i).Value > "" Then Sheet5.[A18].End(xlDown)(2).Resize(, 9).Value = Sheet9.Rows(i).Columns("A:I").Value …​

​

 

[Tech56]

Thanks Marc where does that line go please?

 

[Marc L]

Replace the If … End If block starting the same …​

​

 

[Tech56]

Is this what you meant?

Sub MoveTransX()
    Dim i As Long, lr As Long, lr2 As Long
    Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Recurring Log")
Set s2 = Sheets("Transactions")
Call Unprotect_Transactions


'check if already done for this month
If Month(Date) = Month(Range("J3")) Then
    MsgBox "You have already added these recurring transactions for this month."
    Call Protect_Transactions
    Exit Sub
Else
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 4 To lr
        If s1.Range("B" & i).Value > "" Then Sheet5.[A18].End(xlDown)(2).Resize(, 9).Value = Sheet9.Rows(i).Columns("A:I").Value 
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    'store when last run
    s2.Range("J3") = Date
    MsgBox "This month's recurring transactions have been added."
    Call Protect_Transactions
End If
End Sub

 

[Marc L]

Yes according to your attachment …​

​

 

[Tech56]

Thank you very much for your help.