• Status: Solved
• Priority: Medium
• Security: Public
• Views: 660

# how to insert rows and copy cells in Excel VBA

Using VBA code, I need to go through a range of cells in column P, and where column P contains data, I need to check the quantity on that row (col H). If the quantity on that row is greater than 1.......

Here's the part I don't know how to do:.....

If the quantity on that row is greater than 1, Insert (quantity minus one) rows BELOW that row, AND copy down the data in cells I & J, AND make those cells in column Q yellow interior.

For example, if the code discovers that cell P20 has data and cell H20 = 4, then insert new rows 21, 22, and 23. Copy I20:J20 down to I23:J23. Make Q20:Q23 yellow interior. Then, the next cell in the range and repeat.

Thanks!
0
nbozzy
• 3
• 2
2 Solutions

Commented:
Could you post a sample workbook please?
0

Author Commented:
EE-sample.xls
Attached. Thanks!
0

VBA DeveloperCommented:
Try this:
``````Option Explicit

Sub CheckQuantities()

Dim i As Long
Dim lastRow As Long
Dim rowsToInsert As Integer
Dim startRow As Long
Dim endRow As Long

lastRow = Range("P" & Rows.Count).End(xlUp).Row

For i = 5 To lastRow
Debug.Print i & " - " & lastRow
If Cells(i, "P").Value <> "" Then
If Cells(i, "H").Value <> 1 Then
rowsToInsert = Cells(i, "H").Value - 1
startRow = i
endRow = startRow + rowsToInsert

'Insert rows
Rows(startRow + 1 & ":" & endRow).Insert Shift:=xlDown

'Copy Data
Range("I" & startRow & ":J" & startRow).Copy
Range("I" & startRow & ":J" & endRow).PasteSpecial xlPasteAll

'Highlight Column Q
Range("Q" & startRow & ":Q" & endRow).Interior.ColorIndex = 6
Range("Q" & startRow & ":Q" & endRow).Interior.Pattern = xlSolid

'Reset i counter
i = endRow
lastRow = lastRow + rowsToInsert
Else
End If
Else
End If
Next

End Sub
``````
EE-sample.xls
0

Commented:
If the qty is 1 do you still want yellow?
``````Sub x()

Dim r As Long

For r = Cells(Rows.Count, "G").End(xlUp).Row To 5 Step -1
If Len(Cells(r, "P")) > 0 Then
If Cells(r, "H") > 1 Then
Cells(r + 1, "H").Resize(Cells(r, "H") - 1).EntireRow.Insert shift:=xlDown
Cells(r + 1, "I").Resize(Cells(r, "H") - 1, 2).Value = Cells(r, "I").Resize(, 2).Value
Cells(r, "Q").Resize(Cells(r, "H")).Interior.Color = vbYellow
End If
End If
Next r

End Sub
``````
0

Author Commented:
Thank you both! Both solutions worked, so I will split points.
StephenJR: yes, I do want yellow only if quantity is 1, but I can modify the code to accommodate that. ;)
Broomee9: your code did leave the copy "marquis" visible along with cells selected, but I know how to fix those things.

I appreciate the help and the new knowledge!
0

Author Commented:
I didn't choose a "Best Solution" because that depends upon the particular user. Thanks!
0

## Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.