Variable paste rows based on a cell value

[Guz99]

Hi there,
I am wondering if anyone has a solution for this problem.
I have a table that has value in column C, using this value I want to repeat the paste process on that row this number of times then progress to the next row, until it reaches row 40 in the original sheet (IE. Row 2 - once, Row 3 - twice, Row 4 - six times).
The finished lengths on the original sheet was 3.0m to 12.6m at 0.3m intervals.
I want to paste it on a new sheet using the above senario.

Original data sheet

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product Type	Pack Length	No Beams	Layout No.	6.3	6.0	5.7	5.4	5.1	4.8	4.5	4.2	3.9
2	T2B 300x90	9.6	1	16
3	T2B 300x90	10.2	2	1
4	T2B 300x90	10.2	6	2	1								1
5	T2B 300x90	10.2	1	3		1						1
6	T2B 300x90	10.2	5	4			1				1
7	T2B 300x90	10.2	3	5				1		1
8	T2B 300x90	10.2	3	6					2
9	T2B 300x90	10.2	2	7
10	T2B 300x90	10.8	2	8		1				1
11	T2B 300x90	10.8	1	9
12	T2B 300x90	11.4	1	10
13	T2B 300x90	12.0	2	11		2
14	T2B 300x90	12.0	4	12
15	T2B 300x90	12.6	1	13		1
16	T2B 300x90	12.6	1	14	2
17	T2B 300x90	12.6	2	15
18	T2B 300x90	12.6	1	17
19	T2B 300x90	12.6	3	18									1
20	T2B 300x90	12.6	5	19								1
21	T2B 300x90	12.6	3	20						1
22	T2B 300x90	12.6	2	21					1
23	T2B 300x90	12.6	2	22				1
24	T2B 300x90	12.6	2	23			1
25	T2B 300x90	12.6	5	24		1
26
27
Sheet1

Excel 2007

Creating a new sheet repeating til all data is displayed

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Product Type	Pack Length	Layout	6.3	6.0	5.7	5.4	5.1	4.8	4.5	4.2	3.9
2	T2B 300x90	9.6	16
3	T2B 300x90	10.2	1
4	T2B 300x90	10.2	1
5	T2B 300x90	10.2	2	1								1
6	T2B 300x90	10.2	2	1								1
7	T2B 300x90	10.2	2	1								1
8	T2B 300x90	10.2	2	1								1
9	T2B 300x90	10.2	2	1								1
10	T2B 300x90	10.2	2	1								1
11	T2B 300x90	10.2	3		1						1
12	T2B 300x90	10.2	4			1				1
13	T2B 300x90	10.2	4			1				1
14	T2B 300x90	10.2	4			1				1
15	T2B 300x90	10.2	4			1				1
16	T2B 300x90	10.2	4			1				1
17	T2B 300x90	10.2	5				1		1
18	T2B 300x90	10.2	5				1		1
19	T2B 300x90	10.2	5				1		1
20	T2B 300x90	10.2	6					2
21	T2B 300x90	10.2	6					2
22	T2B 300x90	10.2	6					2
23	T2B 300x90	10.2	7
24	T2B 300x90	10.2	7
25	T2B 300x90	10.8	8		1				1
26	T2B 300x90	10.8	8		1				1
27	T2B 300x90	10.8	9
28	T2B 300x90	11.4	10
29	T2B 300x90	12.0	11		2
30	T2B 300x90	12.0	11		2
Cutting List

Excel 2007

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim r As Long
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1").CurrentRegion
    Set ShNew = Worksheets.Add
    Rng.Rows(1).Copy ShNew.Range("A1")
    For r = Rng.Rows.Count To 2 Step -1
        Rng.Rows(r).Copy
        ShNew.Range("A2").Resize(Rng.Cells(r, 3), Rng.Columns.Count).Insert Shift:=xlDown
    Next r
    ShNew.Cells.EntireColumn.AutoFit
End Sub

 

[VoG]

Try

Sub copydata()
Dim LR As Long, i As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    For j = 1 To Range("C" & i).Value
        Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next j
Next i
End Sub

 

[Guz99]

Thanks to all for the quick response, both work well and I will check running time to get the cleanest code.

Guz.

 

[Guz99]

Ok I have this working fine due to the above coding but my next problem is that the above coding resulted in Sheet16 having data pasted in cells A2:AL91 (the number of rows will vary based on the number of beams I have).
I now want to paste the data from cells F1:AL1 (lengths) based on the number in cells F2:AL500 (number of cuts) to Sheet8 Cell S6:U500 (max 3 cuts per beam)

Excel Workbook
	AA	AB	AC	AD	AE	AF	AG	AH
33			1				1
34			1				1
35			1				1
36			1				1
37			1				1
38				1		1
39				1		1
40				1		1
41				1		1
42				1		1
43				1		1
44				1		1
45				1		1
46				1		1
47				1		1
48				1		1
49				1		1
50				1		1
51				1		1
52					2
53					2
54					2
55					2
56
57
58				1		1
59
Cutting List

Excel 2007

This would then paste into the Sheet8 the following lengths

Excel Workbook
	S	T
20	5.7	4.5
21	5.7	4.5
22	5.7	4.5
23	5.7	4.5
24	5.7	4.5
25	5.7	4.5
26	5.4	4.8
27	5.4	4.8
28	5.4	4.8
29	5.4	4.8
30	5.4	4.8
31	5.4	4.8
32	5.4	4.8
33	5.4	4.8
34	5.4	4.8
35	5.4	4.8
36	5.4	4.8
37	5.4	4.8
38	5.4	4.8
39	5.4	4.8
40	5.1	5.1
41	5.1	5.1
42	5.1	5.1
43	5.1	5.1
Daily Prod Plan 1.1

Excel 2007

Thanks in advance

 

[Guz99]

Or thinking further could this be built into the original coding to do this all in one step?