# Excel - Populate data according to variable

February 2017

## Issue

I want to populate an Excel sheet with a variable number of rows according with a number inserted on a fixed column.

```column A B C D
r1 2009096 1001 19 5
r2 2009097 1002 12 2
r3 2009097 1003 36 6
r4 2009099 1004 10 3
r5 2009099 1005 11 3
r6 2009099 1006 12 2
r7 2009099 1007 13 4
r8 2009101 1008 25 5
r9 2009102 1009 19 5 ```

According with the numbers inserted in column D, i intend to obtain the following result:

```column A B C
r1 2009096 1001 19
r2 2009096 1001 19
r3 2009096 1001 19
r4 2009096 1001 19
r5 2009096 1001 19
r6 2009097 1002 12
r7 2009097 1002 12
r8 2009097 1003 36
r9 2009097 1003 36
r10 2009097 1003 36
r11 2009097 1003 36
r12 2009097 1003 36
r13 2009097 1003 36 ```

## Solution

Assuming that data is in sheet1, starting at row 1 (no header).
Result will be displayed in sheet2.

Correct sheet names and make backup of file first, then see if this macro yield the desired result:
```Sub Test()
Dim lRow, lRow2, x, y As Integer
lRow = Sheets("sheet1").Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Sheet1").Range("E1:E" & lRow)
x = cell.Value
y = 0
Do
Sheets("Sheet1").Select
Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy
lRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Sheet2").Range("A" & lRow2).PasteSpecial
y = y + 1
Loop Until x = y
Next cell
End Sub```

Thanks to TrowaD for this tip.

## Related

Published by aakai1056. Latest update on July 30, 2012 at 05:07 AM by aakai1056.