Paste to next empty row

[Rubber Beaked Woodpecker]

Hi

I use the following code which works well but I need to adjust it a little please.

Sub LogSelection1()
   
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet9")
Set destination = Sheets("Sheet9")

source.Range("AA12").Select
    Selection.Copy
    destination.Range("AF5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AA2").Select

Sheets("Sheet9").Select
         ActiveSheet.Calculate

   
End Sub

However if the code has already been executed and cell AF5 is populated I would like the code to paste in the next available row i.e AF6.

This possible please?

TIA

 

[JoeMo]

Maybe:

Sub LogSelection1()
Dim Lrow As Long

With Sheets("Sheet9")
    If IsEmpty(.Range("AF5")) Then
        Lrow = .Range("AF5").Row
    Else
        Lrow = .Cells(Rows.Count, "AF").End(xlUp).Row
    End If
    .Range("AA12").Copy
    .Cells(Lrow, "AF").PasteSpecial Paste:=xlPasteValues
    .Calculate
End With
End Sub

 

[Rubber Beaked Woodpecker]

Maybe:

Sub LogSelection1()
Dim Lrow As Long

With Sheets("Sheet9")
    If IsEmpty(.Range("AF5")) Then
        Lrow = .Range("AF5").Row
    Else
        Lrow = .Cells(Rows.Count, "AF").End(xlUp).Row
    End If
    .Range("AA12").Copy
    .Cells(Lrow, "AF").PasteSpecial Paste:=xlPasteValues
    .Calculate
End With
End Sub

Thank you for the reply JoeMo

You code works very well but I now need to modify it a little please.

If AF5 is populated then AA12 and is pasted to AF6 - this works fine.

However I also require;
if AF6 is populated then AA12 and is pasted to AF7
if AF7 is populated then AA12 and is pasted to AF8
if AF8 is populated then AA12 and is pasted to AF9

and so on....

Can this be achieved please?

 

[JoeMo]

You are welcome - thanks for the reply. Replace the earlier code with the code below which should satisfy all your requests.

Sub LogSelection1()
Dim Lrow As Long

With Sheets("Sheet9")
    If IsEmpty(.Range("AF5")) Then
        Lrow = .Range("AF5").Row
    Else
        Lrow = .Cells(Rows.Count, "AF").End(xlUp).Row + 1
    End If
    .Range("AA12").Copy
    .Cells(Lrow, "AF").PasteSpecial Paste:=xlPasteValues
    .Calculate
End With
End Sub

 

[Rubber Beaked Woodpecker]

You are welcome - thanks for the reply. Replace the earlier code with the code below which should satisfy all your requests.

Sub LogSelection1()
Dim Lrow As Long

With Sheets("Sheet9")
    If IsEmpty(.Range("AF5")) Then
        Lrow = .Range("AF5").Row
    Else
        Lrow = .Cells(Rows.Count, "AF").End(xlUp).Row + 1
    End If
    .Range("AA12").Copy
    .Cells(Lrow, "AF").PasteSpecial Paste:=xlPasteValues
    .Calculate
End With
End Sub

Brilliant! Thank you

Another edit if I may be so cheeky though, lol

Would it be possible to limit the code to row 36 as I also have data in rows below row 36.

Many thanks RBW

 

[JoeMo]

Maybe something like this if you will always have data in AF36:

Sub LogSelection1()
Dim Lrow As Long

With Sheets("Sheet9")
    If IsEmpty(.Range("AF5")) Then
        Lrow = .Range("AF5").Row
    ElseIf Not IsEmpty(.Range("AF36")) And Application.CountA(.Range("AF5:AF35")) < 31 Then
        Lrow = .Cells(36, "AF").End(xlUp).Row + 1
    Else
        MsgBox "No empty cells available to paste to in the range " & .Range("AF5", "AF35").Address(0, 0)
        Exit Sub
    End If
    .Range("AA12").Copy
    .Cells(Lrow, "AF").PasteSpecial Paste:=xlPasteValues
    .Calculate
End With
End Sub

 

[Rubber Beaked Woodpecker]

Maybe something like this if you will always have data in AF36:

Sub LogSelection1()
Dim Lrow As Long

With Sheets("Sheet9")
    If IsEmpty(.Range("AF5")) Then
        Lrow = .Range("AF5").Row
    ElseIf Not IsEmpty(.Range("AF36")) And Application.CountA(.Range("AF5:AF35")) < 31 Then
        Lrow = .Cells(36, "AF").End(xlUp).Row + 1
    Else
        MsgBox "No empty cells available to paste to in the range " & .Range("AF5", "AF35").Address(0, 0)
        Exit Sub
    End If
    .Range("AA12").Copy
    .Cells(Lrow, "AF").PasteSpecial Paste:=xlPasteValues
    .Calculate
End With
End Sub

Perfect

Thank you very much

 

[JoeMo]

Perfect

Thank you very much

You are welcome - thanks for the reply.