Excel - Populate data according to variable
On April 13,2022 by Tom RoutleyIssue
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.
Article Recommendations
Latest articles
Popular Articles
Archives
- November 2024
- October 2024
- September 2024
- August 2024
- July 2024
- June 2024
- May 2024
- April 2024
- March 2024
- February 2024
- January 2024
- December 2023
- November 2023
- October 2023
- September 2023
- August 2023
- July 2023
- June 2023
- May 2023
- April 2023
- March 2023
- February 2023
- January 2023
- December 2022
- November 2022
- October 2022
- September 2022
- August 2022
- July 2022
- June 2022
- May 2022
- April 2022
- March 2022
- February 2022
- January 2022
- December 2021
- November 2021
- October 2021
- September 2021
- August 2021
- July 2021
- January 2021
Leave a Reply