I am trying to set up a list of data to do a mail merge into Word Labels.
The Current macro copies the required columns (Products) to a new Sheet(Labels). Once copied, the rows are duplicated based on the total quantity so that the correct no of labels are printed.
From here...and I am not sure where to even start, I would like to take the Size of each garment and copy n times based on the count in the column. Sheet 3 shows what the end result should look like. Workbook attached.
Thanks for your help.
- Sub Labels2()
- Dim lastrow As Long
- Dim rng As Range
- lastrow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
- Application.ScreenUpdating = False
- 'Clear Labels Sheet
- 'Copy Columns
- With Sheet2
- .Range("A2:C" & lastrow & ", E2:J" & lastrow).Copy Sheet1.Range("A1")
- End With
- Application.CutCopyMode = False
- 'Remove any blank rows
- N = Cells(Rows.Count, "C").End(xlUp).Row
- For I = N To 1 Step -1
- Set r = Cells(I, "c")
- If IsEmpty(r) Then
- End If
- 'Replicate Rows based on the Value in Col I
- Range("a1:i1").Copy Range("k1")
- On Error Resume Next
- For Each rng In Range("I2", Range("I" & Rows.Count).End(xlUp))
- Cells(Rows.Count, 11).End(xlUp)(2).Resize(rng.Value, 9) = rng.Offset(, -8).Resize(1, 9).Value
- Next rng
- End Sub