Insert blank row after paste in VBA

[Davebro]

I have the following VBA to copy cells into a sheet 2, is there any way that when I paste it inserts a blank row after the paste?


Sub copycolumns()
Dim lastrow As Long, erow As Long

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 10) = "Home" And Sheet1.Cells(i, 1) > 2 Then
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 1)

Sheet1.Cells(i, 2).Copy
Sheets("Sheet2").Cells(erow, 2).PasteSpecial xlPasteValues

Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 3)

Sheet1.Cells(i, 10).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 4)

End If
Next i

Application.CutCopyMode = False
Sheet2.Columns.AutoFit
Range("A1").Select

End Sub

 

[MARK858]

I am a bit confused by your request, erow is finding the first blank row after the data in column A so after the paste the next row will be blank in column A already.
Please clarify your request.

 

[Davebro]

I am a bit confused by your request, erow is finding the first blank row after the data in column A so after the paste the next row will be blank in column A already.
Please clarify your request.

Hi, want the new data to be pasted after the last data has been pasted with a blank row between them

Data
Data
Data

New Data
New Data

 

[MARK858]

Can't you just add 1 to erow?

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[Davebro]

Can't you just add 1 to erow?

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1

No it still paste directly under that last section of data with no space

 

[MARK858]

Sorry pasted lastrow rather than erow

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + 1

or

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row

or

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 2

 

[Davebro]

Sorry pasted lastrow rather than erow

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + 1

or

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row

or

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 2

Regret just the same.

 

[MARK858]

Regret just the same.

Sorry but I don't see how that can be.

 

[Davebro]

Sorry but I don't see how that can be.

These put a space between every item in the paste

Data

Dat

Data

 

[MARK858]

I don't understand what you are saying in your last post.

What you asked for was

is there any way that when I paste it inserts a blank row after the paste?

and later

new data to be pasted after the last data has been pasted with a blank row between them

If you had data down to A5 then

Sheet2.Cells(Rows.Count, 1).End(xlUp) would find A5 and .Row would give the row number 5.
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) would find A5 and Offset by 1 row i.e A6 and .Row would give the row number 6.
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row as above gives Row 6 and show + 1 gives Row 7.

So

Sheet2.Cells(Rows.Count, 1).End(xlUp).Row would paste in Row 5 and overwrite the data.
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row would paste in Row 6 so the row below the data.
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row +1 would paste in Row 7 so 2 rows below the data (data-blank row-paste row).

----------------------------------------------------------------------------------------------------------------------------

By the below I don't think that you want a row after each paste. I think that you want a blank row after each change of data. Which is different.

Can you please clarify with a manual example (about 10 rows) using the boards XL2BB addin of how you want Sheet2 to look at the end please

Hi, want the new data to be pasted after the last data has been pasted with a blank row between them

Data
Data
Data

New Data
New Data