Inserting new rows based on cell value, and copying data to the new rows

[Yann74]

Hi all,

I've been searching a solution to my issue on the board but could not find a working solution that covers my needs.
I'd really appreciate some help to come up with a VBA code that does the following:

I have an Excel file with multiple sheets. In a specific sheet called "xyz" I have data from columns A to BW. Row 1 is for data labels.
The number of rows is variable depending on the source data.

I'd need code to check if column C contains a number or if it's empty.
If it's empty, then proceed to next row.
If it contains a number, then the code should insert the corresponding number of rows below, while copying (and keeping the format) columns G to BW from the origin row to the newly inserted ones.
The loop should process all rows iteratively until there is no data in column A.

For example:
Cell C2 contains "2". Code should insert 2 new rows under row 2 (so 3 and 4) and should copy G2:BW2 to G3:BW3 and G4:BW4 .

Cell C3 (now C5 after the previous rows have been inserted) contains "3". Code should insert 3 new rows under row 5 (so 6, 7 and 8) and should copy G5:BW5 to G6:BW6, G7:BW7 and G8:BW8.

Cell C9 and the the entire row 9 have no data, so the loop should stop.

Many thanks in advance for your kind help!
Yann.

 

[Monke92]

Welcome to the MrExcel board!

Give this a try with a copy of your workbook.

Sub Inert_rows()
  Dim r As Long
 
  For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    With Cells(r, 3)
      If IsNumeric(.Value) And Not IsEmpty(.Value) Then
        Rows(r + 1).Resize(.Value).Insert
        Range(Replace("G#:BW#", "#", r)).Copy Destination:=Range("G" & r + 1).Resize(.Value)
      End If
    End With
  Next r
End Sub

Hi this worked great for me. Except I’m using C as my range to base the inserted rows on. Is it possible to add the ability to fill the new rows with a series based on the data in column a. So basically I have a range of codes in A & B (A=10001 and B=10012, C=B-A) and am adding the rows to be able to properly list all codes in the range. So after the rows are inserted based on the difference, I would like column A to fill after 10001 with 10002-10012.

Thank you for your help.

 

[Peter_SSs]

Hi this worked great for me. Except ..

Welcome to the MrExcel board!

This sounds like it might be quite a different question. I suggest that you start a new thread of your own and detail your requirements there. You can provide a link to this thread if you think that it will assist helpers.

 

[Antony93]

Hello @Peter_SSs ,

First off, thanks a lot for everthing in this wonderful thread. I had the initial same requirement of copying a row "X" times. I was able to successfully tweak the code you gave at the beginning.

But if the value "X" is "0", then the code breaks. I do have the value zero and would need to skip those rows from adding additional rows and move to the next one. So far what I have as code is this:

Sub Inert_rows()
  Dim r As Long
 
  For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    With Cells(r, 12)
      If IsNumeric(.Value) And Not IsEmpty(.Value) Then
        Rows(r + 1).Resize(.Value).Insert
        Range(Replace("A#:W#", "#", r)).Copy Destination:=Range("A" & r + 1).Resize(.Value)
      End If
    End With
  Next r
End Sub

Would be great to get some inputs as I am very much a newbie trying to get things started.

Best,

 

[Antony93]

Dear Peter,

the code works perfectly now, thank you very much.

And more importantly, thanks for pointing out the usage of code tags, I will make sure to use them going forward

 

[Peter_SSs]

the code works perfectly now, thank you very much.

You're welcome. Thanks for the follow-up.

 

[antoanand1981]

Hi,
I've same request but, with minor change,

Cell P2 contains "2". Code should insert 2 new rows under row 2 (so 3 and 4) and should copy data from Q2, R3 and paste in specific col "J"

Cell P3 (now P5 after the previous rows have been inserted) contains "3". Code should insert 3 new rows under row 5 (so 6, 7 and 8) and should copy Q2, R3 and S5. (The Max col of my data in 5, that will col, Q, R, S, T, V

Cell P9 and the the entire row 9 have no data, so the loop should stop.

 

[Vamshi 8143]

Hi All,

Here i am facing to dump data from one sheet to another sheet,

i Have two sheets consider sheet A and Sheet B

In sheet A, I have multiple columns and one column having invoice number which is unique column
In sheet B, I have multiple columns and one column having invoice number and here invoice are duplicates

so i want to dump Sheet B duplicate invoice all rows into below the invoice number row in sheet A

please refer image which we doing it manually

Please provide VBA code to get it automated

Thanks in Advance
Vamshi Krishna

 

[Joe4]

Hi All,

Here i am facing to dump data from one sheet to another sheet,

i Have two sheets consider sheet A and Sheet B

In sheet A, I have multiple columns and one column having invoice number which is unique column
In sheet B, I have multiple columns and one column having invoice number and here invoice are duplicates

so i want to dump Sheet B duplicate invoice all rows into below the invoice number row in sheet A

please refer image which we doing it manually

Please provide VBA code to get it automated

Thanks in Advance
Vamshi Krishna

Please post your question to a new thread of its own, instead of posting it to an old existing unrelated thread.