# VBA to copy and insert row below current row if a value is greater than zero

#### Scottyp123

##### New Member
I have a spreadsheet where rows 12 through 255 I want to copy if a value in column G in that row is > 0 row and insert below the current row. What I would like to do is make it so that once the value in G is set back to 0 or nothing"" the new copied row is removed. Additionally I want to set it up so that it will copy a row only 4 times, after the row is copied 4 times it would no longer make a copy. Not sure if this is possible

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### NdNoviceHlp

##### Well-known Member
Hi Scottyp. It seems like a bit of a challenge but if I understand your needs correctly this seems to work. U need to have an empty column for it to work. I used "H" for this test. I also used "Sheet1" so U will need to adjust these 2 things to suit your needs. HTH. Dave
Code:
``````Sub Test()
Dim Cnt As Integer
For Cnt = 12 To 255
If Sheets("sheet1").Range("H" & Cnt) = 0 Or _
Right(Sheets("sheet1").Range("H" & Cnt), 1) < 8 Then
If Sheets("sheet1").Range("G" & Cnt) > 0 Then
If Sheets("sheet1").Range("H" & Cnt) = 0 Or _
IsNumeric(Left(Sheets("sheet1").Range("H" & Cnt), 1)) Then
Sheets("sheet1").Range("G" & Cnt) = 0
Sheets("sheet1").Rows(Cnt).Copy
Sheets("sheet1").Rows(Cnt + 1).Insert
Sheets("sheet1").Range("G" & Cnt + 1) = vbNullString
Sheets("sheet1").Range("H" & Cnt + 1) = vbNullString
Sheets("sheet1").Range("H" & Cnt) = _
"T" & Sheets("sheet1").Range("H" & Cnt) + 1
Application.CutCopyMode = False
Exit For
Else
Sheets("sheet1").Rows(Cnt + 1).Delete
Sheets("sheet1").Range("G" & Cnt) = 0
Sheets("sheet1").Range("H" & Cnt) = _
Right(Sheets("sheet1").Range("H" & Cnt), 1) + 1
Exit For
End If
End If
End If
Next Cnt
End Sub``````

Replies
1
Views
149
Replies
5
Views
401
Replies
1
Views
72
Replies
5
Views
337
Replies
7
Views
175

1,130,210
Messages
5,640,869
Members
417,174
Latest member
diegomuser

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back