VBA: Append rather than copy over

[jamescooper]

Hello I have the following code which copies from one sheet and pastes from Row 8 in the next. Each time I run the macro it copies over, I would like to change it to append the data.

Any thoughts, thanks.

Sub Add_to ()


Application.ScreenUpdating = False


Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets("Comparison").Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Lastrowa = 8
    For i = 1 To Lastrow
        If Sheets("Comparison").Cells(i, "L").Value = "True" Then
            Sheets("Comparison").Range(Cells(i, "B"), Cells(i, "I")).Copy
            Sheets("My List").Range("B" & Lastrowa).PasteSpecial xlPasteValues
            Lastrowa = Lastrowa + 1
        End If
    
    Next


Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[jmacleary]

Hi there. Change

Lastrowa = 8

to

Lastrowa = Sheets("My List").Cells(Rows.Count, "B").End(xlUp).Row+1

 

[jamescooper]

Thanks, slight issue, needs to be row 8 and down. So first add is from row 8, then append from there.

 

[Fluff]

How about

Lastrowa = Sheets("My List").Cells(Rows.Count, "B").End(xlUp).Row+1
if Lastrowa<8 then Lastrowa=8

 

[jamescooper]

How about

Lastrowa = Sheets("My List").Cells(Rows.Count, "B").End(xlUp).Row+1
if Lastrowa<8 then Lastrowa=8

Thanks Fluff, so the first submission populates from row 8.

Then the second leaves a gap of 2 rows, not dependent on the number of rows I am copying.

 

[Fluff]

No, if there is nothing in col B after row 7, it will paste in row 8, otherwise it will paste in the row below the last row.

 

[jamescooper]

Thanks working now. Great stuff @Fluff

 

[Fluff]

Glad we could help & thanks for the feedback