Solved Macro to insert rows based on input

June 16, 2014 at 09:32:14
Specs: Windows 7
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!

See More: Macro to insert rows based on input

Report •


✔ Best Answer
June 16, 2014 at 12:00:46
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.



#1
June 16, 2014 at 09:43:56
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 in B5 have to do with the value in Column E?

You then say "I want 5 rows inserted after row B" and "...the information in row B copied to the new rows".

B is a Column, not a Row.

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.


Report •

#2
June 16, 2014 at 09:54:44
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.


Report •

#3
June 16, 2014 at 10:00:18
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.


Report •

Related Solutions

#4
June 16, 2014 at 10:17:17
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.

Report •

#5
June 16, 2014 at 12:00:46
✔ 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.


Report •

#6
June 16, 2014 at 15:41:22
Thanks so much, this was just what I was looking for!

Report •


Ask Question