Variable paste rows based on a cell value

Guz99

New Member
Joined
May 17, 2007
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLM
1Product TypePack LengthNo BeamsLayout No.6.36.05.75.45.14.84.54.23.9
2T2B 300x909.6116
3T2B 300x9010.221
4T2B 300x9010.26211
5T2B 300x9010.21311
6T2B 300x9010.25411
7T2B 300x9010.23511
8T2B 300x9010.2362
9T2B 300x9010.227
10T2B 300x9010.82811
11T2B 300x9010.819
12T2B 300x9011.4110
13T2B 300x9012.02112
14T2B 300x9012.0412
15T2B 300x9012.61131
16T2B 300x9012.61142
17T2B 300x9012.6215
18T2B 300x9012.6117
19T2B 300x9012.63181
20T2B 300x9012.65191
21T2B 300x9012.63201
22T2B 300x9012.62211
23T2B 300x9012.62221
24T2B 300x9012.62231
25T2B 300x9012.65241
26
27
Sheet1
Excel 2007

Creating a new sheet repeating til all data is displayed
Excel Workbook
ABCDEFGHIJKL
1Product TypePack LengthLayout6.36.05.75.45.14.84.54.23.9
2T2B 300x909.616
3T2B 300x9010.21
4T2B 300x9010.21
5T2B 300x9010.2211
6T2B 300x9010.2211
7T2B 300x9010.2211
8T2B 300x9010.2211
9T2B 300x9010.2211
10T2B 300x9010.2211
11T2B 300x9010.2311
12T2B 300x9010.2411
13T2B 300x9010.2411
14T2B 300x9010.2411
15T2B 300x9010.2411
16T2B 300x9010.2411
17T2B 300x9010.2511
18T2B 300x9010.2511
19T2B 300x9010.2511
20T2B 300x9010.262
21T2B 300x9010.262
22T2B 300x9010.262
23T2B 300x9010.27
24T2B 300x9010.27
25T2B 300x9010.8811
26T2B 300x9010.8811
27T2B 300x9010.89
28T2B 300x9011.410
29T2B 300x9012.0112
30T2B 300x9012.0112
Cutting List
Excel 2007
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try:

Code:
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
 
Upvote 0
Try

Code:
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
 
Upvote 0
Thanks to all for the quick response, both work well and I will check running time to get the cleanest code.

Guz.
 
Upvote 0
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
AAABACADAEAFAGAH
3311
3411
3511
3611
3711
3811
3911
4011
4111
4211
4311
4411
4511
4611
4711
4811
4911
5011
5111
522
532
542
552
56
57
5811
59
Cutting List
Excel 2007

This would then paste into the Sheet8 the following lengths
Excel Workbook
ST
205.74.5
215.74.5
225.74.5
235.74.5
245.74.5
255.74.5
265.44.8
275.44.8
285.44.8
295.44.8
305.44.8
315.44.8
325.44.8
335.44.8
345.44.8
355.44.8
365.44.8
375.44.8
385.44.8
395.44.8
405.15.1
415.15.1
425.15.1
435.15.1
Daily Prod Plan 1.1
Excel 2007

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

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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
Back
Top