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

Alastair 1210

New Member
Joined
Nov 8, 2014
Messages
3
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ChrisBM

Board Regular
Joined
Sep 22, 2014
Messages
215
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

New Member
Joined
Nov 8, 2014
Messages
3
Thanks for pointing this out Chris. The code numbers were just an example. Any assistance you can give is gratefully received - Thank you.
 

ChrisBM

Board Regular
Joined
Sep 22, 2014
Messages
215
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

Code:
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

New Member
Joined
Nov 8, 2014
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top