Excel Copy Row Using VBA

[fritz.panganiban]

Hi Fritz here, I am new to VBA. Could you help me out what to input in the module page if I need to copy the whole row and paste it to the third row which is actually a blank row. This will be done repeatedly to the items within the sheet. See attached which shows how should the outcome be:

 

[Brianwarnock]

Hell it's 9 years since this thread was created and 8 since I retired, it si uually better to start a new thread than use one so old, but as I had not unsubscribed I got an email.

You are not actually copying the whole row.

I'm rusty, very , but try this

Dim lastrow As Long, fillrow As Long

lastrow = Range("A" & Rows.Count).End(xlUp).Row
fillrow = 4

Do Until fillrow > lastrow + 1
Cells(fillrow, 1) = Cells(fillrow - 2, 1)
Cells(fillrow, 2) = Cells(fillrow - 2, 2)
Cells(fillrow, 3) = Cells(fillrow - 2, 3)
Cells(fillrow, 4) = Cells(fillrow - 2, 4) + Cells(fillrow - 1, 4)
Cells(fillrow, 1) = Cells(fillrow - 2, 1)

Cells(fillrow, 4).Select
Selection.Borders(xlEdgeTop).LineStyle = xlDouble
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlSingle
.Weight = xlThick
End With
fillrow = fillrow + 3
Loop

 

[fritz.panganiban]

You're not rusty yet man, you have done it great. Thanks. However, I have one more request. I have actually data from A1:O1, how could I modify the script if that would be the case?

 

[Brianwarnock]

You should always give the full picture to avoid these situations even f your sample is a cut down version.

the simple way would have been to continue t list of cells being made equal but given the number probably better to do the copy as per below

Dim lastrow As Long, fillrow As Long

lastrow = Range("A" & Rows.Count).End(xlUp).Row
fillrow = 4

Do Until fillrow > lastrow + 1

Rows(fillrow - 2).Copy
Cells(fillrow, 1).PasteSpecial
' now overwrite the cell with the sum
Cells(fillrow, 4) = Cells(fillrow - 2, 4) + Cells(fillrow - 1, 4)
Cells(fillrow, 4).Select
Selection.Borders(xlEdgeTop).LineStyle = xlDouble
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlSingle
.Weight = xlThick
End With
fillrow = fillrow + 3
Loop

 

[fritz.panganiban]

You're the best dude....It works!!!

 

[fritz.panganiban]

Hi Brian,

I would like to invite you on the thread having subject line as
"Insert Blank whole row every after rows meeting the criteria "