asked on

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!

StephenJR

Could you post a sample workbook please?

nbozzy

ASKER

EE-sample.xls
Attached. Thanks!

ASKER CERTIFIED SOLUTION

Tracy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nbozzy

ASKER

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!

nbozzy

ASKER

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