Insert Copied Row 'X-1' Times into Tows Below Depending on Cell Value 'X'

[Alastair 1210]

I'm an advanced user of Excel yet have a minimal understanding of vba coding. I have looked through the forum and can only find vba coding related to inserting copied rows into new worksheets as opposed to inserting them into the same worksheet.

I need some vba code to allow the contents of a row to be copied and inserted into the row below, X-1 times, depending on the X value in a cell.

I've designed a spreadsheet for a friend allowing him to analyse his sheep flock. The information relating to each specific ewe occupies an individual row. There are 150 ewes in his flock - 150 rows from A5 through to A155. Each ewe is identified by a unique code e.g. 1000, 1001, 1002 and recorded in rows B5 to B155.

Each ewe will produce 1, 2, or 3 lambs. Each lamb produced is also given a unique code, recorded in the same row as the ewe that gave birth to it - column C. Each lamb code is therefore linked to the ewe code and both ewe codes and lamb codes can be sorted sequentially.

I need to be able to enter a 1, 2 or 3 into cells A5 through to A155 thereby identifying how many lambs a particular ewe has given birth to.

If a 1 is entered in cell A5, i.e. a particular ewe ‘1000’ has 1 lamb, nothing needs to happen - the unique lamb code ’L1000’ is entered in C5.

If a 2 is entered in A5, i.e. ewe ‘1000’ has 2 lambs, row 5 should be copied and inserted below into row 6 once (All the remaining ewe codes shift down a row) so there will now be 2 rows for ewe code ‘1000’. The unique lamb codes ‘L1000’ and ‘L1001’ can be entered in cells C5 and C6.

If a 3 is entered in A5, i.e. ewe ‘1000’ has 3 lambs, row 5 should be copied and inserted below into rows 6 & 7 (All the remaining ewe codes shift down 2 rows) so there will now be 3 rows for ewe code ‘1000’. The unique lamb codes ‘L1000’, ‘L1001’ and ‘L1002’ can be entered in cells C5, C6 and C7.

I would be very grateful if someone could advise me on the vba code to enable me to do this saving much effort and time.

Many thanks

Alastair

 

[ChrisBM]

Quick point if a ewe (coded 1000) has 2 lambs (L1000 and L1001) this will clash with any lambs ewe (coded 1001) has...

Anyway, will write the copy code now

 

[Alastair 1210]

Thanks for pointing this out Chris. The code numbers were just an example. Any assistance you can give is gratefully received - Thank you.

 

[ChrisBM]

Assuming you have your list of 150 rows of ewes and the number of lambs in column A:


This loops from row 5 to the bottom of the list.

First it checks value in Column A to see whether it is greater than 1.
If it is, it loops from 1 upwards, adding a row and adding appropriate lamb name in column C
If it is exactly 1, it adds one row and writes the lamb name as L & ewe number

For i = 5 to Range("A5").End(xldown).Row

       If Range("A" & i).Value > 1 Then
                  For j = 1 to Range("A" & i).Value
                          Rows(i).Copy
                          Rows(i).Insert Shift:=xlDown
                          Application.CutCopyMode = False
                           Range("C" & i).Value = "L" & Range("B" & i).Value + j-1
                  Next j
                   

                   i = i+j-1
                   Range("C" & i).Value = "L" & Range("B" & i).Value
      
       ElseIf Range("A" & i).Value = 1 Then
                          Rows(i).Copy
                          Rows(i).Insert Shift:=xlDown
                          Application.CutCopyMode = False
                           Range("C" & i).Value = "L" & Range("B" & i).Value

                         i = i+1

       End If

Next

 

[Alastair 1210]

Thank you very much Chris for your code. It’s almost perfect! However due to my bad explanation of my problem your macro is adding 1 too many rows in relation to the value in Column A.

If the value in Column A’s greater than 1 … i.e. 2 … the macro needs to add just 1 row so there are 2 rows in total. Similarly if the value in Column A is 3 the macro needs to add 2 more so there are 3 rows in total. If the value in Column A is exactly 1 then the macro should not add any rows because the row already exists.

I have been trying to tweak your code but am unable to alter it to produce the result as outlined above so any further help would be greatly appreciated. Many thanks