Solved Copy and insert rows based on a value in a cell

September 18, 2013 at 18:12:38
Specs: Windows 7
Hi, I know there are many related questions related on this forum already but I cannot find the code to help in my instance. Please help!

My data range starts in row 4
Column J has numbers
If the data in Column J is >1 then I need to copy that row and insert below by the number in the cell in Column J minus 1.

Please help :)

Mike


See More: Copy and insert rows based on a value in a cell

Report •


✔ Best Answer
September 20, 2013 at 08:24:09
re: "How can I ever repay you"

Pick your favorite charity and send them whatever you think is fitting. Just make sure it's not one of those charities that's spends 90%+ on fundraising, leaving next to nothing for the actual cause.

http://www.cnn.com/2013/06/13/us/wo...

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



#1
September 19, 2013 at 14:45:14
re: ā€¯insert below by the number in the cell in Column J minus 1."

Do you mean that you want to insert a number of rows below the original based on the value in Column J? e.g. J6 contains 4, therefore the row should be copied to Rows 7, 8 and 9. The "new" J10 contains 2, so the row should be copied to J11, etc. is that correct?

Is this something you want to happen as soon as a number is entered/changed in Column J, or is this something that you want to run against an existing sheet?

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


Report •

#2
September 19, 2013 at 14:53:21
Hi and thank you for the reply.
You have it correct, and I only need to run this on existing data.

Report •

#3
September 19, 2013 at 18:50:28
Try this code in a backup copy of your workbook. Macros can not be undone.

Sub Insert_By_J_Value()
'Determine last Row with data in Column J
  lastRw = Cells(Rows.Count, "J").End(xlUp).Row
'Loop through rows in reverse order
    For rw = lastRw To 4 Step -1
'If Column J > 1, insert Rows
     If Cells(rw, "J") > 1 Then
       For newRw = 1 To Cells(rw, "J") - 1
         Cells(rw, "J").EntireRow.Copy
         Cells(rw, "J").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 •

Related Solutions

#4
September 19, 2013 at 20:04:34
Will give it a try and let you know how I get on.

Many thanks...


Report •

#5
September 19, 2013 at 21:17:30
How can I ever repay you, works like a charm.

Report •

#6
September 20, 2013 at 08:24:09
✔ Best Answer
re: "How can I ever repay you"

Pick your favorite charity and send them whatever you think is fitting. Just make sure it's not one of those charities that's spends 90%+ on fundraising, leaving next to nothing for the actual cause.

http://www.cnn.com/2013/06/13/us/wo...

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


Report •

#7
December 17, 2013 at 09:18:30
I have looked at this code since I am trying to do something similar but I admit I am not that familiar with VB. I have a column 'E' that has values of "True" & "False". I need to add a row after each False value. My spreadsheet runs from row 2 to 1453. Can you help?

Report •

#8
December 17, 2013 at 11:26:32
Since thread is marked as Solved, and your question is slightly different than the original question, please start a new thread with a relevant subject line, and reference this thread by including the URL of this thread in your post. That way there will be a thread dedicated to your question in case there are any issues or added requirements that pop up. The reference to this thread will help anyone who wants to respond since this thread includes some code that may just need a little modification.

Thanks!

DerbyDad03
Office Forum Moderator

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


Report •

Ask Question