Excel Copy Row Using VBA (1 Viewer)

fritz.panganiban · Jul 31, 2014

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 · Aug 1, 2014

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

Code:

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 · Aug 1, 2014

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 · Aug 1, 2014

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

Code:

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 · Aug 1, 2014

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

fritz.panganiban · Aug 1, 2014

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 "

Excel Copy Row Using VBA (1 Viewer)

fritz.panganiban

Registered User.

Attachments

Brianwarnock

Retired

fritz.panganiban

Registered User.

Brianwarnock

Retired

fritz.panganiban

Registered User.

fritz.panganiban

Registered User.

Similar threads

Users who are viewing this thread