I am trying to figure out a way to insert multiple rows, based on the input value in column E. For example, if the value in B5 is 5, I want 5 rows inserted after row B. I would also like the information in row B copied to the new rows. Any help with this would be appreciated!

✔ Best Answer

Just happened to have this lying around.... Sub InsertRow_By_E_Value() 'Determine last Row with data in Column E lastRw = Cells(Rows.Count, "E").End(xlUp).Row 'Loop through rows in reverse order For rw = lastRw To 2 Step -1 'If Column E > 0, insert Rows and copy data If Cells(rw, "E") > 0 Then For newRw = 1 To Cells(rw, "E") 'Insert/Copy rows Cells(rw, "E").EntireRow.Copy Cells(rw, "E").EntireRow.Insert shift:=xlDown Next End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Your question is rather confusing.... You say that you want to insert Rows based on a value in Column E, but then you tell us the value in

B5. What does the value inB5 have to do with the value in Column E?You then say "I want 5 rows inserted after

rowB" and "...the information inrowB copied to the new rows".B is a

Column, not aRow.You will need to clarify your question before we can offer any suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yikes! That made no sense at all. Let me try again. What I am hoping to do is insert multiple rows based on the value input in column E. If the value of E2 is 5, then 5 rows would be inserted after row 2, with the information in row 2 copied into the new rows.

More details are required... Are you starting at the top of the sheet (specifcally: which row?) and having these rows inserted after each value in Column E?

e.g.

E2 = 4, Insert/Copy 4 rows for a total of 5 copies of Row 2

E7 (which used to be E3) = 2, , Insert/Copy 2 rows for a total of 3 copies of Row 7

etc.That is the kind of detail we need.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I am starting at row 2 (there is one row of headings), and would like it to be repeated throughout the entire sheet. The example that you provided is exactly what I am looking for.

Just happened to have this lying around.... Sub InsertRow_By_E_Value() 'Determine last Row with data in Column E lastRw = Cells(Rows.Count, "E").End(xlUp).Row 'Loop through rows in reverse order For rw = lastRw To 2 Step -1 'If Column E > 0, insert Rows and copy data If Cells(rw, "E") > 0 Then For newRw = 1 To Cells(rw, "E") 'Insert/Copy rows Cells(rw, "E").EntireRow.Copy Cells(rw, "E").EntireRow.Insert shift:=xlDown Next End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks so much, this was just what I was looking for!

Ask Your Question

Weekly Poll

Do you think Europe's new data protection laws will affect you?

Discuss in The Lounge

Poll History